How to use the Excel DATEDIF Function

The Excel DATEDIF function is used to determine the difference between two separate dates, either in the form of days, months and years.

In this simple guide, we will run through how to use the DATEDIF function in Excel, with a series of examples.

We will cover:

• The formula and syntax
• Working with Days, Months and Years
• Additional outputs that ignore Months and Years
• Examples

Let’s get started

The DATEDIF formula

Unlike working with dates and date formatting in Excel, the formula itself for DATEDIF is quite a simple one and in some ways basically a mathematical function. The syntax is as follows:

=DATEDIF(StartDate, EndDate, UnitOfMeasurement)

One thing you may notice when typing this formula into Excel itself is that unlike pretty much every other formula that there is, Excel won’t actually start auto prompting you with the required inputs! Don’t worry though, if you follow the correct syntax (make sure to use quotation marks for unit) then it will work just fine.

Start Date and End Date are self explanatory, but Unit of Measurement in this case is to determine if we are looking to measure in days, months or years.

When working with these outputs, there are three key values that can be used:

“D” – Returns the difference in number of days

“M” = Returns the difference in number of full months

“Y” – Returns the difference in number of full years

The word full is key here. It does not round up to the next highest number and only includes full months that have passed. If we were measuring the difference between the 1st of March and 28th of April in months using M, we would get an output of 1 because we haven’t reached the 1st of May yet.

Let’s look at some examples and outputs below:

Above we have one example of each of the three outputs in terms of days, months, and years. As we can see, even though the second line looks like roughly 4 months, the output is 3. If we were to change Date B to the 13th of August however, the output quickly changes to 4:

Outside of simply working out the difference between full dates, there are a few additions we can work with when only looking at specific parts of a date, which leads us into the next section.

Ignoring Months and Years

Let’s say we had two dates – 3rd of March 2020, and 20th April 2022 and we only wanted to measure the number of months apart those two dates would be in any calendar year – not the total number of months between the actual dates specifically – thats where the three additional units come in:

“MD” – Returns the difference in days, ignoring the months and years

“YM” – Returns the difference in months, ignoring the years

“YD” – Returns the difference in days, ignoring the years

Essentially here, the first character is what we are ignoring, while the second character is the output.

Using our above example we would use the YM unit, which rather than giving us 25 like the M unit would, simply gives us 1.

Let’s look at these three units in an example, using the exact same three sets of dates used earlier:

As we can see, the first example simply took the difference between the 1st of a month and the 3rd of a month, regardless of what else was in the date. The second example looked at the difference in months (although both are the same year anyway). Finally, the third example looked at the difference between the 25th and 30th of March, regardless of them being 6 years apart.

This sums up our simple guide on how to use DATEDIF in Excel. For more handy guides on working with Excel, be sure to check out our Excel Tips page.