# How do I get the fiscal year to start on a specific date each year in Excel?

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.

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.

You might want to shorten the formula somewhat with `EDATE` and `EOMONTH`. 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

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