Using The DateDif Function in Microsoft Excel
Using The DateDif Function in Microsoft Excel
Have you ever needed to work out the difference between two dates? DateDif allows you to do this, and show the result in Days, Months, Years, Month Days or Year Months.
How to insert a Function
DateDif Syntax and Units
Using Date Functions within DateDif
Working Example
How to Insert a Function
There are a couple of ways to insert a function into an Excel Worksheet.
-
Select the cell where you want your formula to be and select Insert function command, located to the left of the formula bar.
This will bring up the Insert Function Window.
Search for the Function you want to use, and select go.
We will not be able to use this method for our DateDif Function, as it is not recognised, so we will have to type it manually.
-
Select the cell where you want to type your formula, and add the following: =DATEDIF(“01/01/2018”,”24/05/18”,”D”)
This example will return the difference in days between the 1st January 2018, and the 24th May 2018.
DateDif Syntax and Units
Let’s explore the syntax that makes up the DateDif Function.
=DATEDIF(StartDate,EndDate,Unit)
StartDate: The Oldest Date
EndDate: The Newest Date
Unit: How you want your data returned
The units you can use are:
“D” – Days
“M” – Months
“Y” – Years
“MD” – Month Days
“YM” – Year Months
Using Date Functions within DateDif
As well as using a static typed date, you can also use the TODAY() Function, when referring to today’s date. This allows your date differences to be up to date.
In the below example we are going to be working out the age of people, by looking at the difference between their date of birth and today’s date, using the TODAY() Function.
=DATEDIF(“01/03/1985”,TODAY(),”Y”)
Because we used a date function, this formula is now dynamic, and will always show the correct age.
Working Example
Taking the above example one-step further, instead of referring to a static date of birth, we will be using a cell reference. This means that we will be able to copy the formula to other cells.
Number of Years
Number of Months
Number of Days
Years, Months & Days
To achieve this, you need to use the units: “Y”, “YM”, and “MD”. You will also need to use a concatenate function or the &. You can see below that I have also added the Text: Years, Months and Days.
=DATEDIF(A2,TODAY(),"y")& " Years "&DATEDIF(A2,TODAY(),"ym")&" Months "&DATEDIF(A2,TODAY(),"md")&" Days"
About PTR
PTR are a Microsoft Certified Learning Partner and have successfully delivered training in Microsoft Office to hundreds of businesses over more than 25 year.
To learn more about advanced formula join us on one of our Excel Level 2 Courses. To view our other Microsoft Office courses click here.