Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Dates Within Fiscal Year
I have a large worksheet with information as follows:
Example (Col A/Col B/Col C) Date Hired/Date Terminated/Total Days Worked 7-1-01/8-31-01/61 I need to determine how many days were worked in both the 2000-2001 fiscal year (8-1-00--7-31-01) and the 2001-2002 fiscal year. In the example, the desired return would yield 31 and 30 days, respectively. I've had no luck finding such a formula or even something to start with. Does anyone have an bright ideas of how to accomplish this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Dates Within Fiscal Year
Sam --
Howze about: A B C 1 Hired Term Days 2 7/1/01 8/31/01 61 Cell D2 (Days worked in 1st FY): =MIN(DATE(2001,7,31)-A2,B2-A2) Cell E2 (Days worked in 2nd FY): =MIN(B2-DATE(2001,8,1),B2-A2) You may need to add 1 to one formula or other, to take into account the day worked. But you can take it from there. HTH "Sam" wrote: I have a large worksheet with information as follows: Example (Col A/Col B/Col C) Date Hired/Date Terminated/Total Days Worked 7-1-01/8-31-01/61 I need to determine how many days were worked in both the 2000-2001 fiscal year (8-1-00--7-31-01) and the 2001-2002 fiscal year. In the example, the desired return would yield 31 and 30 days, respectively. I've had no luck finding such a formula or even something to start with. Does anyone have an bright ideas of how to accomplish this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Dates Within Fiscal Year
That's great stuff! Thanks a lot so much for your help. You've saved me a
lot of time! Sam "pdberger" wrote: Sam -- Howze about: A B C 1 Hired Term Days 2 7/1/01 8/31/01 61 Cell D2 (Days worked in 1st FY): =MIN(DATE(2001,7,31)-A2,B2-A2) Cell E2 (Days worked in 2nd FY): =MIN(B2-DATE(2001,8,1),B2-A2) You may need to add 1 to one formula or other, to take into account the day worked. But you can take it from there. HTH "Sam" wrote: I have a large worksheet with information as follows: Example (Col A/Col B/Col C) Date Hired/Date Terminated/Total Days Worked 7-1-01/8-31-01/61 I need to determine how many days were worked in both the 2000-2001 fiscal year (8-1-00--7-31-01) and the 2001-2002 fiscal year. In the example, the desired return would yield 31 and 30 days, respectively. I've had no luck finding such a formula or even something to start with. Does anyone have an bright ideas of how to accomplish this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help dealing with a fiscal year rather than a calendar year | Excel Worksheet Functions | |||
Pivot Table: Change dates to fiscal year | Excel Worksheet Functions | |||
Pivot Table: Change dates to fiscal year | Excel Discussion (Misc queries) | |||
Birthdate as fiscal year | Excel Discussion (Misc queries) | |||
Fiscal Year Calculation | Excel Worksheet Functions |