Fiscal year calsulations for past to future dates.

[Nikki]

A date entered in A1 for example is 8/12/2004 that would be fiscal year 2005,
I am looking for a formula that will auto calculate what the correct fiscal
year is based on the date given and put it in B1. I need to go back to 2001
and would like to go as far forward as 2015.

 

[Gary''s Student]

Do you want the first day in the fiscal year to be July 1st??

 

[Nikki]

Please!

Do you want the first day in the fiscal year to be July 1st??

 

[Gary''s Student]

We will use a lookup table. Pick an un-used column, say column H. In H1
enter:

7/1/2001

In H2 enter:

=DATE(YEAR(H1)+1,7,1) and copy down. In H1 thru H23 we see:

7/1/2001
7/1/2002
7/1/2003
7/1/2004
7/1/2005
7/1/2006
7/1/2007
7/1/2008
7/1/2009
7/1/2010
7/1/2011
7/1/2012
7/1/2013
7/1/2014
7/1/2015
7/1/2016
7/1/2017
7/1/2018
7/1/2019
7/1/2020
7/1/2021
7/1/2022
7/1/2023

Put the calendar date in A1 and in B1 enter:

=YEAR(VLOOKUP(A1,H1:H23,1))+1 and format to General

So if A1 contains 7/1/2004, B1 will display 2005
so if A1 contains 6/30/2004, B1 will display 2004

 

[Dave Peterson]

=year(a1)+(month(a1)>6)

You didn't ask about fiscal years and quarters, but...

I like this formula in general--if the fiscal year starts on the first of month
number #:
="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3)

So if the fiscal year starts on July 1st, then I'd use:
="FY"&YEAR(A1)-(MONTH(A1)<7)&"-Q"&INT(1+MOD(MONTH(A1)-7,12)/3)

I also like this style of result:
FY2009-Q1

It makes sorting by that column easier.

 

[Nikki]

Worked great but when I went to copy =YEAR formula it automatically updated
H1:H23 to go to H2:H24, H3:H25 and so on. Is there an easy fix to stay with
H1:H23 but also have A1 continue to update correctly?

Thank you so much for the help! This has saved me a lot of time!

 

[Gary''s Student]

=YEAR(VLOOKUP(A1,H$1:H$23,1))+1

HOWEVER:

Dave Peterson's solution is much simpler and is not limited to a fixed block
of years. I reccommend considering his solution instead.