Return Last Day Of Next Month From Given Date

Re: Add Month In Each Cell

=date(year(a1),month(a1)+2,0)

Please read the This Page which you agreed to when joining. In particular the point on Thread Titles. After you have done so please CLICK HERE and Private Message me or another mod Richie(UK);Dave Hawley with your acceptance, or non-acceptance of the rules and a suggested change for your thread title. Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser. When this is done your thread will be re-opened.

[COLOR="Red"]REMEMBER:[/COLOR] Your question Title must accurately and concisely describe you are trying do, NOT what you THINK is your answer and use only relevant keywords.

[fa]*[/fa]

Re: Return Last Day Of Next Month From Given Date

Andy, Dave:

Could you clarify what the "+2" does in the formula? I don't work with dates much and though I understand the syntax of the functions used here, this piece of the formula isn't clear to me.

Thanks,

AAE

Re: Return Last Day Of Next Month From Given Date

The +2 moves the month number two months' forward ie so given the February date, it moves the month to April (2+2 = 4 ie April). Day zero of April is equivalent (in Excel terms) to 31st March (ie last day of previous month) - hence the formula works.

Richard

Re: Return Last Day Of Next Month From Given Date

Thanks Richard.

I did understand the month was incrementing by the "+2", but how it caused Excel to see the last date of the next month was baffling me. Need to study more about the use of dates.

AAE

Re: Return Last Day Of Next Month From Given Date

If you have the analysis toolpak addin installed:
=EOMONTH(A1,1)
p45cal

Re: Return Last Day Of Next Month From Given Date

Quote

I did understand the month was incrementing by the "+2", but how it caused Excel to see the last date of the next month was baffling me

The day set to Zero, does that, not the +2. E.g -1 in place of Zero returns the 2nd last day and so on...

Re: Return Last Day Of Next Month From Given Date

Thanks Dave. Your statement clarifies it even more when I connect it with the comment from Parsnip.