Fiscal year calsulations for past to future dates.
 

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.

Fiscal year calsulations for past to future dates.
 

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.

Fiscal year calsulations for past to future dates.
 

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.

Fiscal year calsulations for past to future dates.
 

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.

Fiscal year calsulations for past to future dates.
 

=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 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.

--

Dave Peterson

Fiscal year calculations for past to future dates.
 

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.

Fiscal year calculations for past to future dates.
 

=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.