How does Excel add 1 month to a date?

B

Bob

I'm trying to understand how Excel adds 1 month to a date, and how to
correct for it with a formula. I saw a formula for this online, but
now I can't find it.
 
B

Biff

Hi!

Try this:

A1 = 6/28/2006

To add 1 month:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Returns: 7/28/2006

The Date function is also smart. The year, month and days will automatically
roll over dependent of each other. For example, suppose you want to add 1.5
years to a date. That's 18 months. You could do this:

=DATE(YEAR(A1)+1,MONTH(A1)+6,DAY(A1))

Or, you could do this:

=DATE(YEAR(A1),MONTH(A1)+18,DAY(A1))

Biff
 
B

Bob

I'm using the first formula you wrote about
"=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))".

If I start with the date 1/1/06 and add 1 month I get 2/1/06, but if I
start with 1/31/06 and add 1 month I get 3/3/06, so I am wondering how
I fix this in the formula. I've seen this done and can't find it.
 
B

Bob

I'm trying to do this without using Edate since it requires the add-in
and this will get sent to end users that don't have that available.
 
B

Biff

Try this:

=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+1+1,0))))

If A1 = 1/31/2006 the formula returns 2/28/2006.

Biff
 
Top