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](#How to Insert a Function)[DateDif Syntax and Units](#DateDif Syntax and Units)[Using Date Functions within DateDif](#Using Date Functions within DateDif) [Working Example](#Working Example)

How to Insert a Function

There are a couple of ways to insert a function into an Excel Worksheet.

1.  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.

1.  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 DateUnit: 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.