Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
holiday dates bucci Excel Worksheet Functions 4 June 15th 06 09:35 AM
How do I get Excel to automatically calculate salaries actually received in financial year? Kei Excel Discussion (Misc queries) 0 March 3rd 06 11:26 AM
Fiscal Year in date field keith Excel Discussion (Misc queries) 6 February 21st 06 08:09 PM
Fiscal Year Calculation DaGo21 Excel Worksheet Functions 13 February 7th 06 11:16 AM
Date formula: return Quarter and Fiscal Year of a date Rob Excel Discussion (Misc queries) 7 May 11th 05 08:48 PM


All times are GMT +1. The time now is 05:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"