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

swarno01

New member
Joined
Aug 9, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
excel 2010
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!
 
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.
 

Attachments

  • ExcelGuru10160.xlsx
    12.4 KB · Views: 17
Last edited:
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!
 
Back
Top