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 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.
>
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.
> >
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 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.
>> >
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks