Calculate Fiscal Year From A Date

[Determined]

Does anyone know an easy way to calculate a fiscal year based on a date? My fiscal year starts July 1st and ends June 30th. I need to populate the cell next to the cell with a date, with the calculated fiscal year.

[oldchippy]

Does this help?

If you put today's date in A1, then this in B1

=DATE(YEAR(A3),7,1) result = 01/07/2007

and this in C1

=DATE(YEAR(A3)+1,6,30) result = 30/06/2008

[Determined]

I need to determine the fiscal year based on the projected start date. I can also use the project start date and projected end date. I have attached a sample spreadsheet.

[oldchippy]

I've assumed we are using the start date and that this is in cell E2. This formula determines which quarter it is in (Jan-Dec) and if it's less than 3 it's the previous year and if it's greater than 2 it's this year.

=IF(ROUNDUP(MONTH(E2)/3,0)<3,DATE(YEAR(E2)-1,7,1),DATE(YEAR(E2),7,1))

[daddylonglegs]

If your date is in A1 this formula will return the fiscal year for your circumstances

=YEAR(EDATE(A1,-6))

which uses EDATE function from Analysis ToolPak. If you can't use Analysis ToolPak or don't want to, an alternative is

=YEAR(DATE(YEAR(A1),MONTH(A1)-6,1))

edit: the above will give you "2006" for today up to 30th June, then for the next year "2007" is that what you want or do you want it to look like "2006/2007" or similar?

[Determined]

That worked great. Thank you so much. I am curious on how to make it display 2006/2007.

[daddylonglegs]

There are several ways you could do that, possibly one of the easiest

=YEAR(A1)-(MONTH(A1)<7)&"/"&YEAR(A1)+(MONTH(A1)>6)

[Determined]

This also worked great!