DATEDIF Function in Excel

For example, suppose we have 1/1/2013 as a start date in column A5 and 31/7/17 as an end date in column B5. We want to know the difference between the two days in column C. In this scenario, we can use the DATEDIF function in Excel. The formula we can use as:

=DATEDIF(A5, B5,”C”) 

As a result, it will give us 1,672 days.

Syntax

DATEDIF(start_date, end_date, unit)

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

Number of Units Available

  • #1 – Y (Years): Y is 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 entire months between the dates.#3 – D (Days): D is used in a formula to see the number of days between the beginning and end dates.#4 – MD (Days barring years and months): MD is used in a formula to find the date contrast in days, disregarding months and years.#5 – YD (Days barring years): YD is used in a formula to find the date contrast in days, disregarding years.#6 – YM (Months barring days and years): YM is used in a formula to find the date contrast in months, ignoring days and years.

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

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

Example #1 – Number of days between the two dates

Example #2 – Count the number of days ignoring years

  • Step 1: In this example, we will use the following data to find the no. of days, ignoring years.

  • Step 2: First, we must enter the DATEDIF formula in the first row and use “YD” as a unit in a formula.

  • Step 3: Then, we need topress the “Enter” key, and we will find the difference in days between the two dates.

  • Step 4: If we drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.

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

  • Step 1: We will use the following data to find the number of months between the two dates.

  • Step 2: Next, we will enter the DATEDIF formula in the first row and use “M” as a unit in a formula

  • Step 3: Then, we should press the “Enter” key and find the difference in days between the two dates in excelDates In ExcelThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read more.

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

  • Step 1: We will use the following data to find the days, excluding years and months.

  • Step 2: We must enter the DATEDIF formula in the first row, and use “MD” as a unit in a formula.

  • Step 3: We will find the difference in days between the two dates. We will press the “Enter” key.

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

  • Step 1: We will use the following data to find the days, excluding days and years.

  • Step 2: First, we must enter the DATEDIF Formula in the first row and use “YM” as a unit in a formula.

  • Step 3: Then, press the “Enter” key, and we will find the days, excluding days and years.

  • Step 4: If we drag the formula, we will get the result for all the dates. So, Please find the same in the figure shown below.

Example #6 – Find the years.

  • Step 1: Suppose we use the following data to find the years between the two dates.

  • Step 2: We must enter the DATEDIF formula in the first row first and use “Y” as a unit in a formula.

  • Step 3: Next, we need to press the “Enter” key, and we will find the years between the two dates.

  • Step 4: If we drag the formula, we will get the result for all the dates. Therefore, please find the same in the figure shown below.

Advantages

  • The DATEDIF function in Excel is easy to use if we want to find the difference between the two dates.We can use this formula to see the difference between several days, weeks, or months.The DATEDIF function helps us to know the difference between the six parameters.The DATEDIF function is so simple. In many cases, we can use it if the unit is a day, year, or month.The user needs this function only when it requires more control.

Disadvantages

  • The DATEDIF formula in Excel supports only six types of intervals or can help find the difference between only six types of units.It cannot control so many types of units like the first day of the week.Sometimes this function leads to complexity if we only want to find the difference between days, months, or years.Sometimes it may lead to confusion because of many parameters used in one formula.The DATEDIF formula in Excel only gives you the formula if you use the correct date format in excelDate Format In ExcelThe date format in Excel can be changed either from the “number format” of the Home tab or the “format cells” option of the context menu.read more.

Things to Remember

  • For the DATEDIF formula in Excel to figure months accurately, the end date should be greater than the start date; otherwise, the formula may give the  #NUM error.The outcomes returned by DATEDIF and YEAR/MONTH formulas are not constantly indistinguishable because they work dependent on various standards. For example, the Excel DATEDIF work restores the number of whole calendar months between the dates, while the YEAR/MONTH equation works on months’ numbers.We must always select the correct unit in a formula.Excel cannot help us give instructions to fill the DATEDIF formula like other functions.It is also recommended not to use “MD” as units because sometimes it gives a negative value.Always make sure to use data format while calculating the difference between the values.The DATEDIF formula in Excel is also useful while calculating the age.When Excel does not recognize the date given, it results in the #Value! Error because Excel cannot understand the data argument.

This article has been a guide to DATEDIF in Excel. We discuss using the DATEDIF function and Excel examples, and downloadable Excel templates here. You may also look at these useful functions in Excel: –

  • We will use the following data to find the no. of days between 2 dates. Then, we will enter the DATEDIF formula in the first row and use “D” as a unit. Now, we should press the “Enter” key and find the difference in days between the two dates. If we drag the formula, we will get the result for all the dates. So please find the same in the figure shown below.

  • VBA Date FunctionExcel Insert DateConcatenate Date in ExcelToday’s Date in Excel