| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Dates Within Fiscal Year | Excel Discussion (Misc queries) | |||
| Help dealing with a fiscal year rather than a calendar year | Excel Worksheet Functions | |||
| Pivot Table: Change dates to fiscal year | Excel Worksheet Functions | |||
| Pivot Table: Change dates to fiscal year | Excel Discussion (Misc queries) | |||
| How can I hide points for future dates on a Year to Date chart? | Charts and Charting in Excel | |||
Linear Mode
