Should be Easy (Arghhh) Advance a Date by 1 Month

[jase71ds]

I've spent way too much time trying to figure this formula out. I need help.
I have a named range "rng_ReportPeriod" that I manually assign a value to on a monthly basis, and it is ALWAYS the last day of the current month.
Thus, currently rng_ReportPeriod is 3-31-18

Elsewhere in my spreadsheet, I need to increment the months by 1, 2, 3 ... 11
So for the area of the spreadsheet where I need to increment by 1, when rng_ReportPeriod is 3-31-18 (as is currently) I need the resulting 1 month increase to return 4-30-18

I've used this formula:
= DATE( YEAR( rng_ReportPeriod ), MONTH (rng_ReportPeriod + 1 ), DAY( rng_ReportPeriod )

If the next month has the same number of days in it, it works fine, thus if rng_ReportPeriod were 7-31-18, the returned result would be 8-31-18
But in a month like March that has 31 days, if I increment by 1 month, instead of 4-30-18, I get 5-1-18.
I DO understand why this is happening, but I DON'T know what to do about it.

In short - I need to take a given date (which will be last day of the month), increment the month by a series of whole numbers, and ALWAYS get the last day of the month.

Thanks for any help.
Jase.

 

[Norie]

Jase

Try this.

=DATE(YEAR(rng_ReportPeriod), MONTH(rng_ReportPeriod)+2, 0)

 

[louisH]

Hi.

Check the EDATE(Date, n) Function which shifts a date from n month(s)

To get end of month use EOMONTH(Date, n), which shifts a date from n month(s) and returns the last day of it.

 

[jase71ds]

louisH - Thank you - Thank You - THANK YOU!!!
Crap - I practically wasted a whole day yesterday trying to figure this out. Should have come here sooner. Thanks so much!