Calculate Quarter end?

[Gerard]

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!

 

[Thomas [PBD]]

Gerard,

Here is a pretty long nested if, but it will do what you want. Someone
might have a better idea than this:

=IF(A1>DATE(YEAR(A1),3,31),IF(A1>DATE(YEAR(A1),6,30),IF(A1>DATE(YEAR(A1),9,30),DATE(YEAR(A1),12,31),DATE(YEAR(A1),9,30)),DATE(YEAR(A1),6,30)),DATE(YEAR(A1),3,31))

 

[KIM W]

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

 

[Mike H]

A bit shorter

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

Mike

 

[Bob I]

=EOMONTH(A1,3-MONTH(A1))

 

[Bob I]

Oops, make that
=EOMONTH(A1,MOD(3-MONTH(A1),3))

=EOMONTH(A1,3-MONTH(A1))

 

[KIM W]

The brief formula below does not work-- it always returns end of Q1 even if
month is in Q2,3,or 4.

 

[Bob I]

yep, see correction using MOD

The brief formula below does not work-- it always returns end of Q1 even if
month is in Q2,3,or 4.

:

 

[Thomas [PBD]]

Kim,
He amended it quickly after.

Bob I wrote:
Oops, make that
=EOMONTH(A1,MOD(3-MONTH(A1),3))

 

[Craig Gardner]

=EOMONTH(DATE(YEAR(TodaysDate),CEILING(MONTH(TodaysDate),3),1),0)

where TodaysDate could be NOW() or some other date.



ThomasPB wrote:

Kim,He amended it quickly after.
25-Jun-08

Kim
He amended it quickly after. :

Bob I wrote
Oops, make tha
=EOMONTH(A1,MOD(3-MONTH(A1),3)

-
--Thomas [PBD
Working hard to make working easy

:

Previous Posts In This Thread:

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!

Gerard,Here is a pretty long nested if, but it will do what you want.
Gerard

Here is a pretty long nested if, but it will do what you want. Someone
might have a better idea than this

=IF(A1>DATE(YEAR(A1),3,31),IF(A1>DATE(YEAR(A1),6,30),IF(A1>DATE(YEAR(A1),9,30),DATE(YEAR(A1),12,31),DATE(YEAR(A1),9,30)),DATE(YEAR(A1),6,30)),DATE(YEAR(A1),3,31)

--
--Thomas [PBD
Working hard to make working easy

:

To obtain Quarter Number for any date you could
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 dat
Multiply Quarter number by 3 to get Month number of last month in quarte
Use that last month of quarter in EOMONTH function with DAY = 1


:

RE: Calculate Quarter end?
A bit shorte

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

Mik

:

Re: Calculate Quarter end?
=EOMONTH(A1,3-MONTH(A1)

Gerard wrote:

Re: Calculate Quarter end?
Oops, make tha
=EOMONTH(A1,MOD(3-MONTH(A1),3)

Bob I wrote:

The brief formula below does not work-- it always returns end of Q1 even if
The brief formula below does not work-- it always returns end of Q1 even i
month is in Q2,3,or 4

:

Re: Calculate Quarter end?
yep, see correction using MO

KIM W wrote:

Kim,He amended it quickly after.
Kim
He amended it quickly after. :

Bob I wrote
Oops, make tha
=EOMONTH(A1,MOD(3-MONTH(A1),3)

-
--Thomas [PBD
Working hard to make working easy

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk: Parallel Processing with Correlation
http://www.eggheadcafe.com/tutorial...alk-parallel-processing-with-correlation.aspx

 

[Gordon]

=EOMONTH(DATE(YEAR(TodaysDate),CEILING(MONTH(TodaysDate),3),1),0)

where TodaysDate could be NOW() or some other date.

Firstly you are replying to a thread TWO YEARS OLD and secondly please
don't change the subject line.
Oh, I forgot, of course you are posting from the horrible leaching
EggHeadCafe.....