Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Birthdate as fiscal year
What is a formula that will allow me to extract 12 month/year combinations
based on a birthdate to start a fiscal year? For example a birthdate of 07/30/1956 would return July 2006, August 2006...June 2007. A birthdate of 05/22/2001 would return May 2006, June 2006...April 2007. I can handle the year by using a reference field for the starting year, but how do I convert the May or July birthmonth into a series of month/year combos that increment the year when rolling over to January? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Birthdate as fiscal year
Assuming your birth date is in A1, enter this in B1:
=DATE(2006,MONTH(A1),1) Highlight cells B1:C1 and Format the cells using a Custom format of MMMM yyyy. Then in C1 enter this formula: =DATE(YEAR(B1),MONTH(B1)+1,1) and copy this formula into D1:M1 This should wrap at the end of the year. Hope this helps. Pete Clanlabcooker wrote: What is a formula that will allow me to extract 12 month/year combinations based on a birthdate to start a fiscal year? For example a birthdate of 07/30/1956 would return July 2006, August 2006...June 2007. A birthdate of 05/22/2001 would return May 2006, June 2006...April 2007. I can handle the year by using a reference field for the starting year, but how do I convert the May or July birthmonth into a series of month/year combos that increment the year when rolling over to January? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Birthdate as fiscal year
Spot on! I had to modify slightly for my application, but the basic formula
was exactly what I needed. Thank you! "Pete_UK" wrote: Assuming your birth date is in A1, enter this in B1: =DATE(2006,MONTH(A1),1) Highlight cells B1:C1 and Format the cells using a Custom format of MMMM yyyy. Then in C1 enter this formula: =DATE(YEAR(B1),MONTH(B1)+1,1) and copy this formula into D1:M1 This should wrap at the end of the year. Hope this helps. Pete Clanlabcooker wrote: What is a formula that will allow me to extract 12 month/year combinations based on a birthdate to start a fiscal year? For example a birthdate of 07/30/1956 would return July 2006, August 2006...June 2007. A birthdate of 05/22/2001 would return May 2006, June 2006...April 2007. I can handle the year by using a reference field for the starting year, but how do I convert the May or July birthmonth into a series of month/year combos that increment the year when rolling over to January? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Birthdate as fiscal year
Thanks for feeding back.
Pete Clanlabcooker wrote: Spot on! I had to modify slightly for my application, but the basic formula was exactly what I needed. Thank you! "Pete_UK" wrote: Assuming your birth date is in A1, enter this in B1: =DATE(2006,MONTH(A1),1) Highlight cells B1:C1 and Format the cells using a Custom format of MMMM yyyy. Then in C1 enter this formula: =DATE(YEAR(B1),MONTH(B1)+1,1) and copy this formula into D1:M1 This should wrap at the end of the year. Hope this helps. Pete Clanlabcooker wrote: What is a formula that will allow me to extract 12 month/year combinations based on a birthdate to start a fiscal year? For example a birthdate of 07/30/1956 would return July 2006, August 2006...June 2007. A birthdate of 05/22/2001 would return May 2006, June 2006...April 2007. I can handle the year by using a reference field for the starting year, but how do I convert the May or July birthmonth into a series of month/year combos that increment the year when rolling over to January? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
holiday dates | Excel Worksheet Functions | |||
How do I get Excel to automatically calculate salaries actually received in financial year? | Excel Discussion (Misc queries) | |||
Fiscal Year in date field | Excel Discussion (Misc queries) | |||
Fiscal Year Calculation | Excel Worksheet Functions | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) |