Datedif Function not accurate

[IdrisAlMalki]

Hi everyone,
I used DATEDIF function to calculate the period between two dates, but it not showing accurate results.
Example:
someone hire on 01-Oct-2020 and his contract end on 30-Sep-2021, which is one full year, the DATEDIF function showing 0 year and 11 months and 29 days which is not accurate
A1: 01-Oct-2020
A2: 30-Sep-2021
A4: =DATEDIF(A1,A2,"Y")
A5: =DATEDIF(A1,A2,"YM")
A6:=DATEDIF(A1,A2,"MD")

I also used YEARFRAC Function, it showed me 0.99726 instead of 1 year.
A8:=YEARFRAC(A1,A2,1) ---- > 0.997260273972603
A9:=YEARFRAC(A1,A2,0) ---- > 0.997222222

can anyone tell me a formula where it showing the result is 1 year and 0 month and 0 day
thank you

 

[jasonb75]

It is accurate, the dates shown are a difference of 364 days, which is one day short of a full year.

If you want the length of the period inclusive of the start and end dates (which is not the same thing as the difference between 2 dates) then you need to add 1 to the end date.

For clarity, 1-Oct-2020 to 2-Oct-2020 is a 2 day period, but only a difference of 1 day.

 

[IdrisAlMalki]

Thanks, Jasonb75 for your quick response and clarification.
Yes, now my formula is fixed. I added 1 after the day. =DATEDIF(A1,A2,"MD")+1
Now it became 0 year and 11 months and 30 days. I divided the days over 360 instead of 365 and it shows 1 year exactly.

Also for YEARFRAC, I subtracted one from the start date, =YEARFRAC(A1-1,A2), and now it is working

 

[jasonb75]

Yes, now my formula is fixed. I added 1 after the day. =DATEDIF(A1,A2,"MD")+1

No no no, you need to add it to the end date, not the whole formula.

=DATEDIF(A1,A2+1,"MD")

If you add it to the end of the formula then it will be incorrect for other differences.

Also for YEARFRAC, I subtracted one from the start date,

You can add 1 to the end date, or subtract 1 from the start date. Either method will work with any formula but adding 1 is the most common way of doing it.

 

[IdrisAlMalki]

Thanks alot @jasonb75 again. I adjusted my formula now ?

 

[Peter_SSs]

Regarding DATEDIF not being accurate, do bear in mind the following warning from Microsoft regarding the DATEDIF function

Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios.