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. 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.


"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!

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!

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!

=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!

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!
>
>

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!
>
>

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!
>>
>>

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!
> >
> >

=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

"KIM W" wrote:

Previous Posts In This Thread:

On Wednesday, June 25, 2008 10:27 AM
Gerar wrote:

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!

On Wednesday, June 25, 2008 10:49 AM
ThomasPB wrote:

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

"Gerard" wrote:

On Wednesday, June 25, 2008 11:01 AM
KIM wrote:

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


"Gerard" wrote:

On Wednesday, June 25, 2008 11:07 AM
Mike wrote:

RE: Calculate Quarter end?
A bit shorte

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

Mik

"Gerard" wrote:

On Wednesday, June 25, 2008 11:31 AM
Bob I wrote:

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

Gerard wrote:

On Wednesday, June 25, 2008 11:41 AM
Bob I wrote:

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

Bob I wrote:

On Wednesday, June 25, 2008 11:50 AM
KIM 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

"Bob I" wrote:

On Wednesday, June 25, 2008 11:56 AM
Bob I wrote:

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

KIM W wrote:

On Wednesday, June 25, 2008 12:02 PM
ThomasPB 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

"KIM W" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk: Parallel Processing with Correlation
http://www.eggheadcafe.com/tutorials...rrelation.aspx