0

I have a quick question that probably seems obvious - maybe I'm conceptualising the problem in the wrong way, so thought I'd get some outside help.

I need a way to determine how much of a given period (start and end dates held in separate columns) falls within a financial year (1/7/XXXX - 30/6/XXXX). If I could determine the number of days in that period that falls in the financial year it'll be a lifesaver.

Really appreciate the help!

2

If you have start date in A2 and end date in B2 then you can use this formula to determine how many days fall in 2013/14 financial year

=MAX(0,MIN($B2,DATE(2014,6,30))-MAX($A2,DATE(2013,7,1))+1)

That will include both start date and end date.

If you want to calculate for multiple years then put the years in C1 across and change formula to this in C2 copied across

=MAX(0,MIN($B2,DATE(C$1+1,6,30))-MAX($A2,DATE(C$1,7,1))+1)

  • Took me a while to figure out what a "financial year" was (despite it mentioned in the problem), but this works, even for leap day. – Raystafarian Mar 1 '14 at 11:43
  • Sorry for the late acceptance - this works perfectly! Thanks for the help on this! – analystic Mar 9 '14 at 14:07
0

I improved upon Barry Houdini's answer. I must thank him for getting to this point. His ideas of using max and min was much simpler than what I was trying to accomplish. Thanks!

My formula takes into account the FY of the start date, so you can use the formula across single or multiple FYs without manually changing the formula. It accounts for leap years and includes the 1st day in the calculation.

Essentially, this is a [end date] minus [start date] formula. If the [end date] is before the FY ends, use the [end date]. If the [end date] is after the FY ends, use September 30 (9/30) with the same FY as the [start date].

end date = [B1]

start date = [A1]

formula for end date =MIN(B1,DATE(IF(MONTH(A1)<=9,YEAR(A1),YEAR(A1)+1),9,30))

formula for start date =A1

formula together =MIN(B1,DATE(IF(MONTH(A1)<=9,YEAR(A1),YEAR(A1)+1),9,30))-A1+1

Examples in m/d/y format:

1/1/17 to 1/2/17 is 2 days

1/1/17 to 9/30/17 is 273 days

1/1/17 to 10/1/17 is 273 days

1/1/17 to 10/1/18 is 273 days

9/30/17 to 10/1/18 is 1 day

To change the FY to end 3/31 =MIN(B1,DATE(IF(MONTH(A1)<=3,YEAR(A1),YEAR(A1)+1),3,31))-A1+1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.