Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

DATEDIF Function

Calculates the number of days, months, or years between two dates

What is the DATEDIF Function?

The DATEDIF Function in Excel is categorized under Date/Time functions. As a financial analyst, we may require the number of days, months or year between two dates. The DATEDIF function helps us calculate the difference.

 

Formula

=DATEDIF(start_date,end_date,unit)

 

The DATEDIF function includes following arguments:

  1. Start_date – It is a required argument. As the name suggests, it is the initial date of the period.
  2. End_date – It is a required argument. It represents the last, or ending, date of the period.
  3. Unit – It is time unit in which we want the information.

 

UnitReturns
“Y”Difference in complete years. The function returns the number of complete years in the period.
“M”Difference in complete months. The function returns the number of complete months in the period.
“D”Difference in complete Days. The function returns the number of complete Days in the period.
“MD”It calculates the difference between the days in start_date and end_date. The months and years of the dates are ignored here.
“YM”It calculates the difference between the months in start_date and end_date. The days and years of the dates are ignored here.

 

Interestingly, DATEDIF is a hidden function in Excel. As seen below, Excel doesn’t list this function when we type =DATE in a cell. Hence, we need to understand thoroughly the syntax of this function.

 

How to use the DATEDIF Function in Excel?

To understand the uses of this function, let’s consider a few examples:

 

Example 1

Let’s say we wish to find the number of days between May 10, 2015 and July 10, 2017. The formula to use would be:

 

DATEDIF Function

 

We can, on a similar basis, provide cell references. For example, the following formula counts the number of days between the dates in cells A1 and B1:

 

DATEDIF Function - Example 1

 

DATEDIF Function - Example 1a

 

As we are aware that Excel stores each date as a serial number beginning with January 1, 1900, so we can put numbers corresponding to the dates directly in the formula. For example, =DATEDIF(42134, 42205, “m”)

However, this method is not fully reliable as date numbering varies with different computer systems.

 

Example 2

Suppose we receive a list of debtors with outstanding debts. We wish to calculate the number of days from the day they were required to pay until today, that is July 31, 2017.

 

DATEDIF Function - Example 2

 

The solution we get is:

 

DATEDIF Function - Example 2a

 

Suppose you want the difference in weeks. To find out how many weeks there are between two dates, we can use the DATEDIF function with “D” unit to return the difference in days, and then divide the result by 7.

The formula to be used is:

 

DATEDIF Function - Example 2b

 

The solution we get is in weeks:

 

DATEDIF Function - Example 2c

 

If we want, we can wrap the DATEDIF formula in the ROUNDDOWN function, which always rounds the number towards zero:

=ROUNDDOWN((DATEDIF(B2, A2, “d”) / 7), 0)

 

Example 3

To count the number of whole months between dates, DATEDIF function with “M” unit can be used. The formula =DATEDIF(start_date, end_date, “m”) compares the dates in A2 (start date) and B2 (end date) and returns the difference in months:

Suppose we are given the following dates:

 

DATEDIF Function - Example 3

 

The formula used is:

 

DATEDIF Function - Example 3a

 

The solution we get is:

 

DATEDIF Function - Example 3b

 

If you wish to ignore years, the formula to use would be =DATEDIF(start_date, end_date, “ym”)

The data given are:

 

DATEDIF Function - Example 3c

 

The formula used is:

 

DATEDIF Function - Example 3d

 

The solution we get is:

 

DATEDIF Function - Example 3e

 

Example 4

Suppose a flower store is required to send flowers to its customers. We wish to calculate the next anniversary date. The data given are:

 

DATEDIF Function - Example 4

 

The formula to be used is:

 

DATEDIF Function - Example 4a

 

The solution we get is:

 

DATEDIF Function - Example 4b

 

To learn more, launch our free Excel crash course now!

 

Notes about the DATEDIF Function in Excel

  1. #NUM! error – Occurs when the start_date is greater than the end_date.
  2. #VALUE! Error – Occurs when Excel cannot recognize the date given, i.e. when the date argument is invalid.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!