Problem with DATE function

[engbojkov]

Hello,
I’m having the following problem with the DATE function in excel. As you probably know when you add one month to a certain date using the formula: DATE(YEAR(A1);MONTH(A1)+1;DAY(A1)) you get one month shift from the date in cell A1 i.e the results is the exact same date, one month after. But when the date in A1 is end of the month and the following month is shorter than the initial one – there is a problem for me as excel results not at the end of the next month but at the beginning of the month after next. Example:
A2= DATE(YEAR(A1);MONTH(A1)+1;DAY(A1))
When A1=31.01.2015 the result in A2 is 03.03.2015
When A1=03.02.2015 the result in A2 again is 03.03.2015 i.e. there is “time gap” of three days

Obviously for such cases when the following month is shorter excel follows the logic: match the exact date until fulfilling the available dates, after that add 31 days no matter you end up in the month after next or not.
In my case though this logic ruins the model I’m building as I need to keep within the month. When the formula results into the month after next that triggers some unwanted formulas and results.
Can you suggest elegant way to stay within the month, no matter if it is shorter or longer than the previous one?

Regards!

[daddylonglegs]

Try using EDATE to add months, i.e.

=EDATE(A1,1)

It won't go beyond the end of the month, so

=EDATE(DATE(2014,1,31),1)

gives you 28th February

[engbojkov]

Works perfect!
Thanks!