Excel 2016 DATEDIF Function Issues

Question

My DATEDIF function is returning another date as opposed to the number of days in between the specified dates.

Here's the function:   =DATEDIF(B5,C5,"d")

And here's what the function is returning (B5 and C5 are the first two cells, respectively. The third cell is the output of the above function):

6/4/2018	6/6/2018	1/2/1900

The function should be returning just thenumber two, not as a date. I've tried reformatting the date "style" of the cells to no avail. 

Here's the full grid. Maybe more examples will help show what's going on here! Thanks so much in advance for any help.

6/4/2018	6/6/2018	1/2/1900
6/7/2018	6/8/2018	1/1/1900
6/11/2018	6/15/2018	1/4/1900
6/18/2018	6/22/2018	1/4/1900
6/25/2018	6/29/2018	1/4/1900

Answer 1

Mitchell, change the format of the cell to numeric so it shows the value as an integer rather than a date.

Answer 2

Did you check that the cell formatting is number? It looks like you set it to date (makes sense because date is Jan 2nd 1900...).

Please let us know.

Thanks

Ugo

Answer 3

Hi Ugo!

Thanks for the suggestion. I changed it to C5-B5 and the output is the same. Any other ideas you have would be greatly appreciated!

Mitchell

Answer 4

That worked! Thanks Duane!

Answer 5

Ciao Mitchell,

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.

For further information and workaround, please check this article here:

https://support.office.com/en-us/article/DATEDI...

If you just want the number of days in between the two dates, anyway, please try the following:

=C5-B5

(result, as per your example, should be 2).

Please let us know if this solves your issue.

Thanks

Ugo