Excel =Date and Leap Years

=date(year(a1) + b1, month(a1) + b2, day(a1))

Yes, that's what I am doing. It does not account for leap years doing it that way. All it does is numerically sum the years and months, checks to see if it's a real day (ex: Feb 30 bumped to March 2), and that's it. I need it to consider if the dates it's moving through are leap years and adjust the resulting date accordingly.

It does not account for leap years doing it that way.

For example, ...?

For example it counts dates passing through year 2010 (365 days) the same as dates passing through 2012 (366 days).

2 years on to 2011-01-25 should not be 2013-01-25 as there is an extra day in 2012. Excel doesn't seem to factor this in, however. This function makes it seem like 2011 and 2012 have the same number of days. It is summing the years without any consideration to years it advances through. The only consideration to leap years comes when the final date is generated and it checks to see if that day exists during that year. For example 2008-02-29 + 4 years = 2012-02-29 whereas 2009-02-29 + 3 years = 2011-03-01. That's as far as it takes leap years into consideration. I need it to add a day to the final date for every leap year that is added on to the initial date.

Perhaps you need to reconsider exactly where you're adding the time.

The DATE function will behave as you suggest if you add the time to the day portion:

Please Login or Register  to view this content.

But you'll need to seriously consider the error you are incorporating, especially when you want to add a "standard" month of 30 days.

Cheers,

Originally Posted by thesonofdarwin

... because I'd manually have to enter +1 day for dates passing through a leap year...

Correct me if I'm wrong, but your initial description seemed to indicate that (adding a day) is precisely what you were trying to avoid. The point is, the DATE function IS allowing for leap years when you seem to be wanting it not to do so. Are you not expecting the function to add only 365 days (to reach an expiry) even when there is a leap year?

Hello thesonofdarwin, I think you are mistaken.

When you add a year which is 366 days long you are already adding 366 days, if you add on another day you will be adding 367 days and effectively double-counting the leap day.

Test that by putting 1-jan-2012 in A1 and 2-jan-2013 in B1 and in C1 use

=B1-A1

and format C1 as general

result is 367, the number of days between those 2 dates