Excel DATEDIF Function


Home >> How to Use Excel Tutorial >> Excel Formulas List >> Excel DATEDIF Function



In this tutorial, you will learn a quite hidden formula which is DATEDIF function in excel. We call it hidden because excel DATEDIF function seems to be not “recognized” by this spreadsheet tool, cannot be seen if you do the typing to write it, unlike other formulas. DATEDIF function in excel can be used to calculate the difference between two dates, in terms of day, month, or year.


Why We Need to Learn About DATEDIF Function in Excel?


Data consists of various kinds. One of the kind often found is date.

There are three groups of numeral data that we can get from a date: day, month, and year. Sometimes, we only need to do processing on only one of the three groups but feel difficult to separate it from its date. The calculation process which is often done for this kind of data such as finding the difference between two dates becomes a hard and time-consuming thing to do.

A case example where we need one of those groups is when we want to know the working period of all employees in the company or probably when we want to analyze how long a product will get to the hand of a customer after it is produced from the data that we have. We might only want to know the difference of days, months, or years from each of the case and normal subtraction calculation in the spreadsheet cannot do it accurately. Even if it can, then we need to process the date data first before we can subtract them to get accurate result as desired. Thus, the process will be more difficult and longer.

To solve the processing of date data kind problem, there is excel DATEDIF function to help us do it. DATEDIF function in excel can directly calculate the difference of days, months or years from two date data depending on our needs by inputting both of the dates that we want to subtract and which data group that we want have subtraction process on.

For sure, thus, excel DATEDIF function will be a very useful formula to be understood if we often analyze data related to dates.


What is Excel DATEDIF Function?


Excel DATEDIF function is a function with the usage objective to find the difference of days, months, or years from two different dates. DATEDIF function in excel is very useful because it will take a longer time to separate day, month, or year to be subtracted from a date if we do normal spreadsheet subtraction process.

General explanation about the inputs in this formula is as follows:


=DATEDIF(first_date, last_date, unit)


Note:
  • first_date = earlier date that will be the subtractor
  • late_date = later date that will be the subtracted
  • unit = the determinant of the kind of difference that you want to find



How to Use DATEDIF Function in Excel?


The following will explain how to use excel DATEDIF function. The usage of DATEDIF function in excel itself cannot be seen unlike other formulas when you write it in your cell so you need to understand excel DATEDIF function well first before using it. DATEDIF function in excel needs the inputs of earlier, later date, and the unit which represents which data group you want to find the difference from and what kind of subtraction you want to do. All of the possibilities for this unit input of DATEDIF function in excel will be explained in the following table:

UnitCalculate the Difference ofAdditional Note
Yyear-
Myearincludes year
Ddayincludes month and year
MDdayexcludes month and year
YMmonthexcludes year
YDdayexcludes year


You need to note about those “includes” and “excludes” thing for every unit in the “Additional Note” column on the above table.

For example, you want to calculate the difference between 1 January 2019 and 2 February 2020. If you use “D” unit to calculate your day’s difference, then you will get the result of 397 (because it calculates the day’s difference in the year and month too) but if you use “YD” unit, then you will get the result of 32 (ignores the day’s difference in the year but still calculates the days in the month)


Using Excel DATEDIF Function

  1. Type equal sign ( = ) in the cell where you want to put the result in

    Excel DATEDIF Function - Screenshot of Step 1

  2. Type DATEDIF (can be with large and small letters) and open bracket sign after =

    Excel DATEDIF Function - Screenshot of Step 2

  3. Type earlier date or cell coordinate where the date is after open bracket sign followed by a comma sign ( , )

    Excel DATEDIF Function - Screenshot of Step 3

  4. Type later date or cell coordinate where the date is after the comma sign then type comma sign ( , )

    Excel DATEDIF Function - Screenshot of Step 4

  5. Type the unit between quotation marks (“”) (can be with large or small letter) or cell coordinate where the unit is. Make sure this unit input is in line with your need for the calculation process using this formula!

    Excel DATEDIF Function - Screenshot of Step 5

  6. Type close bracket sign

    Excel DATEDIF Function - Screenshot of Step 6

  7. Press Enter
  8. The process is done!

    Excel DATEDIF Function - Screenshot of Step 8



Exercise


After you learned from the tutorial of DATEDIF function in excel above, you can practice while also deepening your understanding by doing this exercise!

Download the file from the following link and answer the questions. Please download the answers if you have done answering all the questions and sure about the results!

Link to download the exercise file:
Download here

Questions

  1. What is the difference between the years in the second row?
  2. What is the difference between the months by calculating the years too in the third row?
  3. What is the difference between the days by ignoring the months and years in the fourth row?

Link to download answer file:
Download here


Additional Notes


Make sure you input the dates in order on excel DATEDIF function. The earlier date must be in the most front position and the later must be behind. If wrongly inputted, then the formula will not work well and result in #NUM error.



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert