DATEDIF in Excel

DATEDIF Function in Excel

DATEDIf is a date function used to find the difference between two given dates, now these differences can be in years months or days, it takes two dates as an arguments and one keyword to specify which type of difference is desired for the output, the output for this function is integer and the method to use this function is as follows =DATEDIF(Start Date, End Date, Unit), however, this function is compatible with excel 2000 and previous versions of excel.

Syntax

DATEDIF(start_date, end_date, unit)

  1. Start_date – the initial date of the period you want to calculate.
  2. Unit: The time unit to utilize while ascertaining the difference between two dates.
  3. End_date – the ending date of the period.

Number of Units Available

  • #1 – Y (Years): Y used in a formula to find the number of complete years between beginning and end dates.
  • #2 – M (Months): used in a formula to find the number of complete months between the dates.
  • #3 – D (Days): D used in a formula to find the number of days between the beginning date and end date.
  • #4 – MD (Days barring years and months): MD used in a formula to find the date contrast in days, disregarding months and years.
  • #5 – YD (Days barring years): YD used in a formula to find the date contrast in days, disregarding years.
  • #6 – YM (Months barring days and years): YM used in a formula to find the date contrast in months, disregarding days and years.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: DATEDIF in Excel (wallstreetmojo.com)

How to use the DATEDIF Function in Excel? (with Examples)

You can download this DATEDIF Function Excel Template here – DATEDIF Function Excel Template

Example #1 – Number of days between the two dates

  1. Use the following data to find the no. of days between 2 dates.


    DATEIF in excel example 1.1

  2. Enter DATEDIF formula in the first row, use D as a unit in a formula.


    DATEDIF in excel example 1.2

  3. Press Enter, you will find the difference in days between the two dates.


    DATEDIF in excel example 1.3

  4. Now, if we will drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.


    DATEDIF in excel example 1.4

Example #2 – Count the number of days ignoring years

  • Step 1: Use the following data to find the no. of days ignoring years.
DATEDIF in excel example 2.1
  • Step 2: Enter DATEDIF Formula in the first row, use YD as a unit in a formula.
DATEDIF in excel example 2.2
  • Step 3: Press Enter, you will find the difference in days between the two dates.
DATEDIF in excel example 2.3
  • Step 4: Now, if we will drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.
DATEDIF in excel examplee 2.4

Example #3 – Count the number of months between the two dates

  • Step 1: Use the following data to find a number of months between the two dates.
DATEDIF in excel example 3.1
  • Step 2: Enter DATEDIF Formula in the first row, use M as a unit in a formula
DATEDIF in excel example 3.2
DATEDIF in excel example 3.3
  • Step 4: Now, if we will drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.
DATEDIF in excel example 3.4

Example #4 – Find the days excluding years and months.

  • Step 1: Use the following data to find the days, excluding years and months.
DATEDIF in excel example 4.1
  • Step 2: Enter DATEDIF Formula in the first row, use MD as a unit in a formula.
DATEDIF in excel example 4.2
  • Step 3: Press Enter, you will find the difference in days between the two dates.
DATEDIF in excel example 4.3
  • Step 4: Now, if we will drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.
DATEDIF in excel example 4.4

Example #5 – Find the days excluding days and years.

  • Step 1: Use the following data to find the days excluding days and years
example 4.1
  • Step 2: Enter DATEDIF Formula in the first row, use YM as a unit in a formula.
example 5.2
  • Step 3: Press Enter, you will find the days, excluding days and years.
example 5.3
  • Step 4: Now, if we will drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.
example 5.4

Example #6 – Find the years.

  • Step 1: Use the following data to find the years between the two dates
example 6.1
  • Step 2: Enter DATEDIF Formula in the first row, use Y as a unit in a formula
example 6.2
  • Step 3: Press Enter, you will find the years between the two dates.
example 6.3
  • Step 4: Now, if we will drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.
example 6.4

Advantages

  1. DATEDIF in excel is very easy to use if you find the difference between the two dates.
  2. If you want to find the difference between a number of days, week, or month, use this formula.
  3. DATEDIF helps you find the difference between the six parameters.
  4. DATEDIF is so simple because of simplicity, and it can be used in many of the cases if the unit is a day, year, or month.
  5. The user needs this function only when it requires more control.

Disadvantages

  1. DATEDIF formula in excel supports only six types of intervals or able to help to find the difference between only six types of units.
  2. It cannot control so many types of units like the first day of the week.
  3. Sometimes this function leads to complexity if you only want to find the difference between the day, month, or years.
  4. Sometime it may lead to confusion because of so many parameters used in one formula.
  5. DATEDIF formula in excel only gives you the formula if you are using the correct date format in excel.

Things to Remember

  1. For the DATEDIF formula in excel to figure months accurately, the end date should be greater than the start date; otherwise, the formula gives the error the #NUM error.
  2. The outcomes returned by DATEDIF and YEAR/MONTH formulas are not constantly indistinguishable in light of the fact that they work dependent on various standards. The Excel DATEDIF work restores the number of complete calendar months between the dates, while the YEAR/MONTH equation works on months’ numbers.
  3. Always select the correct Unit in a formula.
  4. Excel will not help you to give instruction to fill DATEDIF formula like other functions.
  5. It is also recommended that don’t use MD as units because sometimes it gives a negative value.
  6. Always make sure to use data format while calculating the difference between the values.
  7. DATEDIF formula in excel is also useful while you are calculating the age.
  8. When Excel does not recognize the date given, it results in the #Value!error because excel won’t understand the data argument.

Recommended Articles

This has been a guide to DATEDIF in Excel. Here we discuss how to use the DATEDIF function along with excel examples and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *