Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 6th 06, 01:15 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
 
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  
Old April 6th 06, 01:39 PM 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  
Old April 6th 06, 04:03 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
 
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  
Old April 6th 06, 04:44 PM 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  
Old April 6th 06, 05:03 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
 
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  
Old April 6th 06, 05:12 PM 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  
Old April 6th 06, 06:54 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
 
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 08:02 AM
Another Date issue. TimM Excel Worksheet Functions 1 November 17th 05 01: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 04:11 PM


All times are GMT +1. The time now is 12:55 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017