DATEDIF problem

[vt1701]

Hello,

I'm working at an application (internal use) requiring compatibility with the serial numbers generated by Excel for date values. During the testing phase, I've noticed strange issues (failing tests) which I've taken as my application's errors. Then I've created a table and I've noticed a strange behavior of the DATEDIF Excel function when using YD unit and MD unit (at least, this one is documented), sometimes the manual calculation of the difference between the days of the years (the E column in the attached picture) gives a different result than the DATEDIF function. Where am I wrong?

[#2 · Accepted solution]

@vt1701 

Let's take your first example: 29/03/4976 to 15/10/5346 (dd/mm/yyyy format).

DATEDIF calculates the number of days as follows:

1. Move the maximum number of whole years forward from 29/03/4976 to end up before 15/10/5346. You arrive at 29/03/5346.
2. Count the days between 29/03/5346 to 15/10/5346. This is 200.

Why did you get a different result? Because 4976 is a leap year. This causes the difference between 01/01/4976 and 29/03/4976 to be 1 day more. But you don't want to count from 01/01, but from 29/03. (Since 5346 is not a leap year, you don't count an extra day there)

So I would say that DATEDIF is correct here.

[#3]

Thank you very much! Now I understand how the Excel function works, I was doing the other way in my algorithm.