Forum Discussion
Problem with DATEDIF function
Hi. I'm trying to calculate the difference between two dates in Excel. I have used the DATEDIF function to do this in the past, but it doesn't seem to be working correctly this time. Instead of returning the number of days between dates, it is giving me another date. As an example, here is the calculation I am trying to make:
Start date: 2007-01-21 (cell G2)
End date: 2019-11-16 (cell H2)
Previously I would have used =DATEDIF(G2,H2,"D"), but this is giving me a result of 1912-10-25.
I've tried using the Date & Time formula "DAYS" as well, and in the Function Arguments box it lists the number of days between the two dates as 4682 (the correct answer), but the formula result remains 1912-10-25. The main issue is I have a column of 400 entries, so I want to input the formula in the top row and drag down to auto-calculate the remaining entries. Any idea as to what the issue could be in this case? As mentioned, I have used this formula successfully in the past.
Thank you for your help!
6 Replies
Try formatting the result cell as General. Excel sees you are calculating with dates and thinks you want to display the result as a date as well. Dates are stored as numbers, so whatever the outcome of your formula is, it will get displayed as the date that is as many days after 1 January 1900.
By te way, DATEDIF is not needed for this kind of calculation. See attached example.
- I just ran across this solution and you are exactly correct. I was getting different dates in January, 1900. Formatting as General fixed it. Thanks!
Riny_van_Eekelen I have a problem in using datedif ,it's not giving total experience properly, Example: it's giving only 5.1 or 5.9 (here 5 stand for year 1 stand for month, it's same with other mentioned results),it's not generating results as 5.11 year ,I request someone to assist me on this
9606806084 You need to clarify and give more detail. DATEDIF is not an official Excel function and know to to give wring results in certain circumstances, what's your formula? How does the underlying data look like?
Riny_van_Eekelen Would =DATEDIF formula be the right formula to use if I need to calculate the age of my students in Years and Months on September1st? because I seem to have work on one excel file and not the other despite formatting the date cells as cells and the result cells as general or text. I was thinking maybe one excel file is older than the other but still I count make it work.
Please help!
Dalal1310 Not sure where to look. If DATEDIF works for you, go for it. Just be aware that is might not always be accurate.
https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c
