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!

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!

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!

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!

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!

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!

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!

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!

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!