How do I select same day of month, 3 mths apart when day 31 of mth
 

I want a table of dates for every 3 months after a starting date (could be
15th, 30th 31st etc). Difficulty when it is for example March 31 . The
formula :
=DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date))
provides July 1. The correct date should be June 30.

The above formula works fine with days in the mid-month eg. 15th

I am wanting to avoid using the EOMONTH due to some starting dates are
mid-month. Formula should also deal with February!
Thanks

How do I select same day of month, 3 mths apart when day 31 of mth
 

Please try this one out, with the start date in A1. Does it
accommodate mid-month start dates properly?
=EOMONTH(A1,3)-(DAY(EOMONTH(A1,0))-DAY(A1))

How do I select same day of month, 3 mths apart when day 31 of mth
 

Maybe:
=DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY(A1),DAY(EOMONTH (A1,3))))

"JohnNZ" wrote:

I want a table of dates for every 3 months after a starting date (could be
15th, 30th 31st etc). Difficulty when it is for example March 31 . The
formula :
=DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date))
provides July 1. The correct date should be June 30.

The above formula works fine with days in the mid-month eg. 15th

I am wanting to avoid using the EOMONTH due to some starting dates are
mid-month. Formula should also deal with February!
Thanks

How do I select same day of month, 3 mths apart when day 31 of
 

Brilliant - thanks Dave O!!

"Dave O" wrote:

Please try this one out, with the start date in A1. Does it
accommodate mid-month start dates properly?
=EOMONTH(A1,3)-(DAY(EOMONTH(A1,0))-DAY(A1))

How do I select same day of month, 3 mths apart when day 31 of
 

Thanks, but seemed to not be consistent with day of month later on in the
year eg. after 6, or 9 months.

"JMB" wrote:

Maybe:
=DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY(A1),DAY(EOMONTH (A1,3))))

"JohnNZ" wrote:

I want a table of dates for every 3 months after a starting date (could be
15th, 30th 31st etc). Difficulty when it is for example March 31 . The
formula :
=DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date))
provides July 1. The correct date should be June 30.

The above formula works fine with days in the mid-month eg. 15th

I am wanting to avoid using the EOMONTH due to some starting dates are
mid-month. Formula should also deal with February!
Thanks

How do I select same day of month, 3 mths apart when day 31 of
 

Which date(s) tripped up the formula?

"JohnNZ" wrote:

Thanks, but seemed to not be consistent with day of month later on in the
year eg. after 6, or 9 months.

"JMB" wrote:

Maybe:
=DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY(A1),DAY(EOMONTH (A1,3))))

"JohnNZ" wrote:

I want a table of dates for every 3 months after a starting date (could be
15th, 30th 31st etc). Difficulty when it is for example March 31 . The
formula :
=DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date))
provides July 1. The correct date should be June 30.

The above formula works fine with days in the mid-month eg. 15th

I am wanting to avoid using the EOMONTH due to some starting dates are
mid-month. Formula should also deal with February!
Thanks

How do I select same day of month, 3 mths apart when day 31 of
 

Sorry JMB this was my first time using on-line community, and I got my
replies mixed. Your formula was perfect. Thanks again.

"JMB" wrote:

Which date(s) tripped up the formula?

"JohnNZ" wrote:

Thanks, but seemed to not be consistent with day of month later on in the
year eg. after 6, or 9 months.

"JMB" wrote:

Maybe:
=DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY(A1),DAY(EOMONTH (A1,3))))

"JohnNZ" wrote:

I want a table of dates for every 3 months after a starting date (could be
15th, 30th 31st etc). Difficulty when it is for example March 31 . The
formula :
=DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date))
provides July 1. The correct date should be June 30.

The above formula works fine with days in the mid-month eg. 15th

I am wanting to avoid using the EOMONTH due to some starting dates are
mid-month. Formula should also deal with February!
Thanks

How do I select same day of month, 3 mths apart when day 31 of mth
 

On Wed, 24 May 2006 18:48:01 -0700, JohnNZ
wrote:

I want a table of dates for every 3 months after a starting date (could be
15th, 30th 31st etc). Difficulty when it is for example March 31 . The
formula :
=DATE(YEAR(starting date),MONTH(starting date)+3,DAY(starting date))
provides July 1. The correct date should be June 30.

The above formula works fine with days in the mid-month eg. 15th

I am wanting to avoid using the EOMONTH due to some starting dates are
mid-month. Formula should also deal with February!
Thanks

You can use the EDATE function.

You should also be sure to always reference back to the starting date,
otherwise three months after 9/30 will be 12/30 when you probably want 12/31.

One method of referencing back to the starting date, with the starting date in
A1 and your table in column A:

A2: =edate($A$1,ROWS($1:1)*3)

and copy/drag down.


--ron