Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Adding XY days to date in cells | Excel Discussion (Misc queries) | |||
Another Date issue. | Excel Worksheet Functions | |||
Format to display date as Quarter | Excel Discussion (Misc queries) | |||
Date Math Problem | Excel Worksheet Functions |