DATEDIF returning incorrect value

Not applicable

I'm using the excel function =DATEDIF(start_date,TODAY(),"Y") and for each line this function runs under, I'm getting the incorrect value. 


For example: I have an employee who's start date is 3/11/2018, and I want to know how many years they've worked for the company as of TODAY(), but the formula is showing 3 and not 4. There aren't any errors, I'm not asking to calculate days or months, and I've even substituted today's actual date instead of the TODAY(), but the answer is always 3. And this this incorrect value is happening on each employee. 



6 Replies

@Deleted If TODAY() is March 11, 2022 DATEDIF should return the correct 4 years. If, however, you entered the formula when TODAY() was March 10, 2022 you get 3, as DATEDIF returns the number of whole years. Microsoft warns that DATEDIF is not an official Excel function and that it can return incorrect results.

Screenshot 2022-03-11 at 18.09.27.png 

@Riny_van_Eekelen Thank you, I'll keep that in mind when I use that formula.


Are you on desktop or online version? If the latest check time zone for SharePoint/OneDrive.


Today is 2022-03-11

@Sergei Baklan 


I am using =DATEDIF(A1,TODAY(),"d") in the cell where the return is 9/6/1901 instead of the # of days


2022-07-21$21,500.00 9/6/1901


What am I doing wrong?



Select the cell with the DATEDIF formula and set the number format to General.


In addition, you may use


applying General or Number format to the resulting cell