# Thread: Formula for amount of days within a date range that fall within a fiscal year

1. ## Formula for amount of days within a date range that fall within a fiscal year

Hi, I am trying to build a formula to calculate amount of days within a date range that fall within a fiscal year
I have a simple formula to calculate delta days (=M1-D1) however this just provides me total days between the dates in Column M & D.

Column A (Start Date) Column M (End Date)
13/07/2018 16/01/2019
Delta days for the above dates = 187

What I now need to calculate is how many of these 187 days fall within my Fiscal year

Fiscal year is from 01/11/2017 to 31/10/2018

Any ideas, I have been playing with a whole bunch of "IF" formulas, but I can't get it perfect.

Here are some sample data if you can solve this for me I would be very happy camper
Column D Column M
13/07/2018 16/01/2019
11/03/2019 03/06/2019
25/08/2017 18/09/2017
18/05/2018 18/05/2018
21/08/2018 26/11/2018

FYI, If the there are no days within the Fiscal I would need it to show as 0
Thanks again!

2. The formula can be quite short; the likes of:
Code:
`=MAX(0,MIN(\$R\$2,M2)-MAX(\$Q\$2,A2))`
See attached.
It may need a tweak or two if it's a day out; the end of the fiscal year might really be 1/11/2018 for this calculation. See also note in cell R2.

Check the difference in dates in column T also, especially that T22 is giving you the expected result.

3. Thanks this seems to have worked I will need to play with it to ensure it makes sense through out my doc.
Much appreciated, I will let you know if it all works out or if I have additional questions.
Thank again!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•