#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Quarter End Date

What is a formula that can be used to find the nearest quarter end,
when the fiscal year has a non-standard start date (ie. November fiscal
year, current date is 2/28/2005, quarter end should be 4/30/05)?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Quarter End Date

=DATE(YEAR(A1),(INT((MONTH(A1)+1)/3)+1)*3-1,0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
ups.com...
What is a formula that can be used to find the nearest quarter end,
when the fiscal year has a non-standard start date (ie. November fiscal
year, current date is 2/28/2005, quarter end should be 4/30/05)?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Quarter End Date

Thanks very much--this is perfeect. Is there a way to generalize for
any given fiscal year start (ie. use 11 for November as an input)?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Quarter End Date

=DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3+CHOOSE(MOD(MONTH(fiscal_start_date
),3)+1,0,1,2),0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
Thanks very much--this is perfeect. Is there a way to generalize for
any given fiscal year start (ie. use 11 for November as an input)?



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Quarter End Date

Is there a way to generalize for any fiscal month start using the
following formula:

DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,DAY(0))

This I find to be best because it can be used to find half-year and
year-end intervals as well.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Quarter End Date

Do you mean you want the last date of the month for any date you put in A1,
or do you mean something else?

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



wrote in message
oups.com...
Is there a way to generalize for any fiscal month start using the
following formula:

DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,DAY(0))

This I find to be best because it can be used to find half-year and
year-end intervals as well.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Quarter End Date

Not really. Trying to write a generalizable formula to find the
nearest month, quarter, half-year, or year-end, but able to accomodate
different fiscal year start dates.

For example, if the Fiscal Year begins in November and today's date is
today, 4/6/06.

Nearest month end: 4/30/06
Nearest quarter end: 4/30/06
Nearest half-year end: 4/30/06
Nearest year-end: 10/31/06

If the fiscal year began in January, then it would be:

Nearest month end: 4/30/06
Nearest quarter end: 6/30/06
Nearest half-year end: 6/30/06
Nearest year-end: 12/31/06

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
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
Adding XY days to date in cells satucha Excel Discussion (Misc queries) 1 November 25th 05 09:02 AM
Another Date issue. TimM Excel Worksheet Functions 1 November 17th 05 02:58 AM
Format to display date as Quarter Christine Excel Discussion (Misc queries) 5 October 19th 05 03:30 AM
Date Math Problem Dkline Excel Worksheet Functions 4 March 4th 05 05:11 PM


All times are GMT +1. The time now is 12:29 AM.

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"