I was recently asked a question about Excel, which led me to discovering an Excel function I never knew existed, and would probably have never found by accident (certainly not by clicking on the Fx button or seeing it listed by Excel when starting to type another function).
The question was “How can I show the difference between two dates in years, months and days?” and the answer is “Use the DATEDIF function.”
However, the DATEDIF function doesn’t feature as an option when using the Fx button, and Excel doesn’t even provide any prompts when you type the function.
The syntax for the function is =DATEDIF(start date,end date,unit to display) – and either of the two dates can be TODAY() if you want to use the current date.
To create/use the function to show the number of whole years in the selected cell, type =DATEDIF( then locate or type in the reference for the start date (or TODAY()), type in a comma followed by the reference for the end date (or TODAY(), type in another comma followed by “Y”) and press [Enter].
Repeat the process in other cells to extract the months and days, using one of these in the last part:
- “M” for the total number of months
- “YM” for the number of months remaining after the number of whole years has been removed
- “D” for the total number of days
- “YD” for the number of days remaining after the number of whole years has been removed
- “MD” for the number of days remaining after the numbers of whole years and whole months have been removed
For example: with the date 1/1/2000 in cell A1 and 29/3/2019 in cell A2:
- =DATEDIF(A1,B1,"Y") would display the number 19
- =DATEDIF(A1,B1,"YM") would display the number 2 and
- =DATEDIF(A1,B1,"MD") would display the number 28
These 3 cells would then show that the difference between the two dates is 19 years, 2 months and 28 days - brilliant!