0

I used the formula listed here to generate a fiscal year based on the current month. I have an odd requirement where she wants the fiscal year to start on 6/20. For example, any dates ranging from 6/20/2014 to 6/19/2014 will Fiscal Year 15.

Can someone help me figure out the formula?

My formula is currently is:

=IF(MONTH(J2)>=7,YEAR(J2)+1,YEAR(J2))

Column J has the date that I need to calculate the fiscal year for.

So, I am close where anything from July (7) to December (12) will be calculated as the current year + 1.

  • How about posting your solution as an answer? – fixer1234 Nov 26 '14 at 1:21
1

Awww, figured it out. I had to put in a nested IF to check if the month = 6 and date is >= 20.

=IF(AND(MONTH(P2)=6,DAY(P2) >= 20),YEAR(P2)+1,IF(MONTH(P2)>=7,YEAR(P2)+1,YEAR(P2)))

I don't like to use nested IF statements, but it seems like a necessary evil this time.

0

You might want to shorten the formula somewhat with EDATE and EOMONTH.

            enter image description here

The formula in B2 is,

=YEAR(EOMONTH(EDATE(A2,6),0)+(DAY(A2)>19))

Fill down as necessary. I've applied a custom number format using F\Y 0000. This leaves the year as a true number that can be used in future calculation while visibly displaying the fact that it is an offset fiscal year. From past experience, I do not recommend that you concatenate FY as a string as it renders the value useless for calculations.

EDATE function

EOMONTH function

  • This looks good. I'll give it a try after thanksgiving. Cheers! – Sun Nov 26 '14 at 15:07
0

You can do this very simply by "offsetting" the date by the requisite number of days, e.g. 20th June until 1st January is 195 days therefore you can use this formula

=YEAR(P2+195)

format result cell as number with zero decimal places

  • What about leap years? – Sun Nov 26 '14 at 15:05
  • It's still 195 days from 20th June to 1st January (that's counting forwards) in any year, so the solution isn't affected by leap years - it will work for any date in any year, within Excel's date restrictions – barry houdini Nov 26 '14 at 15:26

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.