Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
Is there a formula similiar to EOMONTH that can used to calculate the end of
a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
Gerard,
Here is a pretty long nested if, but it will do what you want. Someone might have a better idea than this: =IF(A1DATE(YEAR(A1),3,31),IF(A1DATE(YEAR(A1),6,3 0),IF(A1DATE(YEAR(A1),9,30),DATE(YEAR(A1),12,31), DATE(YEAR(A1),9,30)),DATE(YEAR(A1),6,30)),DATE(YEA R(A1),3,31)) -- --Thomas [PBD] Working hard to make working easy. "Gerard" wrote: Is there a formula similiar to EOMONTH that can used to calculate the end of a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
To obtain Quarter Number for any date you could use:
=ROUNDUP(MONTH(A1)/3,0) Then put that in the EOMONTH Function as follows... =EOMONTH(DATE(YEAR(A1),3*ROUNDUP(MONTH(A1)/3,0),1),0) Here is text explanation: Obtain Quarter number of date Multiply Quarter number by 3 to get Month number of last month in quarter Use that last month of quarter in EOMONTH function with DAY = 1 "Gerard" wrote: Is there a formula similiar to EOMONTH that can used to calculate the end of a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
A bit shorter
= DATE(YEAR(A1),3*TRUNC((MONTH(A1)-1)/3)+4,0) Mike "Gerard" wrote: Is there a formula similiar to EOMONTH that can used to calculate the end of a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
=EOMONTH(A1,3-MONTH(A1))
Gerard wrote: Is there a formula similiar to EOMONTH that can used to calculate the end of a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
Oops, make that
=EOMONTH(A1,MOD(3-MONTH(A1),3)) Bob I wrote: =EOMONTH(A1,3-MONTH(A1)) Gerard wrote: Is there a formula similiar to EOMONTH that can used to calculate the end of a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
The brief formula below does not work-- it always returns end of Q1 even if
month is in Q2,3,or 4. "Bob I" wrote: =EOMONTH(A1,3-MONTH(A1)) Gerard wrote: Is there a formula similiar to EOMONTH that can used to calculate the end of a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
yep, see correction using MOD
KIM W wrote: The brief formula below does not work-- it always returns end of Q1 even if month is in Q2,3,or 4. "Bob I" wrote: =EOMONTH(A1,3-MONTH(A1)) Gerard wrote: Is there a formula similiar to EOMONTH that can used to calculate the end of a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Quarter end?
Kim,
He amended it quickly after. :) Bob I wrote: Oops, make that =EOMONTH(A1,MOD(3-MONTH(A1),3)) -- --Thomas [PBD] Working hard to make working easy. "KIM W" wrote: The brief formula below does not work-- it always returns end of Q1 even if month is in Q2,3,or 4. "Bob I" wrote: =EOMONTH(A1,3-MONTH(A1)) Gerard wrote: Is there a formula similiar to EOMONTH that can used to calculate the end of a quarter? For example, for a date of 5/28/08, is it possible to display 6/30/08, which is quarter end? If not any suggestions to an alternative formula (or string of formulas)? MM/DD/YY format would be preferred. Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate a quarter | Excel Discussion (Misc queries) | |||
how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) | |||
Quarter End Date | Excel Worksheet Functions | |||
First and Last Day of the Quarter | Excel Worksheet Functions | |||
Calculate % variance on previous quarters. Quarter %, etc. pivot | Excel Discussion (Misc queries) |