Datedif

[manoj_th]

Hello Sir,

When using the above formula for the following dates:
01/01/2016 and 12/31/2045, the result is "29", where as it should return "30" which is correct "=DATEDIF(H9,H10,"Y")" (H9 and H10 are cell address).
But if I use "=DATEDIF(H9,H10,"M")" and the result divided by 12, getting "30".

How to get the answer "30" by using "Y" in the formula.
Please suggest.
Manoj

 

[Nebu]

Hi:

Just add 1 to your date if formula to get inclusive no: of years. If you divide your date if function with 12 (as mentioned in your post) you will get something like 29.9166666666667 not exactly 30.

Thanks

 

[Khalid NGO]

Hi Manoj,

Datedif calculates number of complete years "between" two dates.
If you simply do:
=YEAR(H10)-YEAR(H9)
result would be same i.e. 29

another example is:
H9 = 01-Jan-16
H10 = 31-Dec-16

You may want the result = 1, but the formula will calculate 0

Just add +1 at the end, as Nebu advised:
=DATEDIF(H9,H10,"y")+1

Note: Datedif is not much reliable function as discussed earlier in the forum.

Regards,

 

[Deepak]

Try this..

=YEARFRAC(H9,H10)

=INT((H10-H9)/365)

Actually dates are stored in excel as 1900+Days.

 

[pecoflyer]

There seems to be a bug in DATEDIF in certain XL versions http://www.mrexcel.com/forum/excel-questions/302028-bug-datedif-excel-2007-a-2.html