Prev: Formula Help...
Next: Excel - Expense tracking-multiple users
From: Zoe on 15 Apr 2010 18:16
So, here is the formula I'm using. Some cells it's adding 2 or 3 days to the
MONTH and some cells it's not. I don't WANT it to add 2 days. It should just
be adding 5 years and leaving the month and day the same... what is happening
here?

=IF(J5="","",DATE(YEAR(J5)+5,MONTH(J5),DAY(J6)))
From: Zoe on 15 Apr 2010 18:20
Never mind!!! I'm ridiculous! I figured it out!!!!

"Zoe" wrote:

> So, here is the formula I'm using. Some cells it's adding 2 or 3 days to the
> MONTH and some cells it's not. I don't WANT it to add 2 days. It should just
> be adding 5 years and leaving the month and day the same... what is happening
> here?
>
> =IF(J5="","",DATE(YEAR(J5)+5,MONTH(J5),DAY(J6)))
From: T. Valko on 15 Apr 2010 18:51
Not all years have the same number of days:

Leap year = 366 days
Not leap year = 365 days

Not all months have the same number of days:

Jan = 31 days
Feb = 28 or 29 days
June = 30 days

So, when you add years and, depending on what day is in J6, Excel adjusts
the resulting accordingly.

A couple of simple examples:

A1 = 2/29/2008

=DATE(YEAR(A1)+2,MONTHA1),DAY(A1))

That returns the date 3/1/2010

You want to add 2 years to the date but 2010 is not a leap year and Feb 29
2010 is not a valid date so Excel returns the next valid date, 3/1/2010.

A1 = 8/31/2010

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

That returns the date 10/1/2010

You want to add 1 month to the date but Sept only has 30 days and 9/31/2010
is not a valid date so Excel returns the next valid date 10/1/2010.

Consider this formula:

A1 = 1/1/2010

=DATE(YEAR(A1),MONTH(A1),35)

In essence, Excel is "smart" enough to know that January 35th 2010 is really
February 4th 2010.

You might be able to use the EDATE function to get the results you expect.

A1 = 2/29/2008

=EDATE(A1,60) returns 2/28/2013

Format as Date. That will return the date 5 years from the date in A1. (12
months per year * 5 years = 60 months)

=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) returns 3/1/2013

--
Biff
Microsoft Excel MVP


"Zoe" <Zoe(a)discussions.microsoft.com> wrote in message
news:C0C2DB29-EA9C-4FA3-95BD-48BCC1ACFCE4(a)microsoft.com...
> So, here is the formula I'm using. Some cells it's adding 2 or 3 days to
> the
> MONTH and some cells it's not. I don't WANT it to add 2 days. It should
> just
> be adding 5 years and leaving the month and day the same... what is
> happening
> here?
>
> =IF(J5="","",DATE(YEAR(J5)+5,MONTH(J5),DAY(J6)))


 | 
Pages: 1
Prev: Formula Help...
Next: Excel - Expense tracking-multiple users