How does Excel add 1 month to a date?

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.

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

<[email protected]> wrote in message
news:[email protected]...
> 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.
>

edate function
=edate(start_date, 1)

<[email protected]>
???????:[email protected]...
> 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.
>

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.


Biff wrote:
> 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
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >

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.

bobocat wrote:
> edate function
> =edate(start_date, 1)
>
> <[email protected]>
> ???????:[email protected]...
> > 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.
> >

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

<[email protected]> wrote in message
news:[email protected]...
> 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.
>
> bobocat wrote:
>> edate function
>> =edate(start_date, 1)
>>
>> <[email protected]>
>> ???????:[email protected]...
>> > 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.
>> >
>