Question on DATEDIF with Leap Years consideration

[rickyckc]

Hi,

Am I correct to say these....

If I do a datedif between say 01 Jan 1900 and 01 Jan 2010, I should deduct one day.

Like wise if I do a datedif between 01 Jan 1800 and 01 Jan 2010, I should deduct two days.

If I do a datedif between 01 Mar 1900 and 01 Jan 2010, the result is correct

?

 

[Jon von der Heyden]

Are you literally just looking for the number of days between the dates? If so you could just use EndDate - StartDate.

 

[Jon von der Heyden]

Also see here, which includes leap year explanation: http://www.cpearson.com/excel/datedif.aspx

 

[rickyckc]

Hi Jon,

Actually, I'm thinking whatever formula I use.

What I'm trying to ask is, Am I correct that I should deduct 29 Feb 1900, 29 Feb 1800, etc to get a accurate result.

 

[Andrew Poulsom]

What do you mean by accurate? 29 Feb is a day like any other isn't it?

 

[rickyckc]

The year 1700, 1800, 1900 is NOT a Leap Year but I think Excel is recognising it as a existing date.

 

[rickyckc]

....meaning you will be able to find example 29th Feb 1900 as a date which is wrong ?

 

[Andrew Poulsom]

Excel does indeed incorrectly treat 1900 as a leap year:

http://support.microsoft.com/kb/214326

You can't enter a date before 1 Jan 1900, so 1700 and 1800 aren't relevant.

 

[rickyckc]

I've never tried before 01 Jan 1900, so I didn't know. Guess now I do.

And thanks for that link. Now I also know ONLY 1900 is affected.