DATEDIF function in Excel

Posted in: Digital Data & Technology Group, IT Literacy

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!

Posted in: Digital Data & Technology Group, IT Literacy

Responses

  • (we won't publish this)

Write a response

  • Thanks for that - it'll be useful. But, what other functions are there 'hidden'? How did you find this one?

    • Good question: I found DATEDIF through googling various phrases around 'Excel differences between dates'. I have since tried googling 'hidden Excel functions' - I've only found DATEDIF. If anyone knows of any others, please inform us in a comment.

  • I really like these Excel tips being sent out! This one is particularly useful, please keep them coming!!

    • Thanks Lucie. Glad to hear you find them useful. There will be more on their way in the coming weeks.