=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.
--
Gary''s Student - gsnu200843


"Nikki" wrote:

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" wrote:

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



--
Gary''s Student - gsnu200843


"Nikki" wrote:

Please!

"Gary''s Student" wrote:

Do you want the first day in the fiscal year to be July 1st??
--
Gary''s Student - gsnu200843


"Nikki" wrote:

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.