Calculating Date Differences in Excel

DATEDIF function in Microsoft Excel

Calculating DATE differences

We can calculate the no. of days, weeks, months & years by using excel’s built in DATEDIF function.

Microsoft has not provided a proper documentation for the DATEDIF function, hence to understand its syntax we have made it easier for you. 

The Syntax for DATEDIF:

=DATEDIF(start_date, end_date, type)

Arguments:

• Start_date: Give the starting date in Date format to avoid any errors. You can format the date using format cells. Use DATE function to enter date without any errors.
• End_date: Give the end date for which you want to calculate the years, days, weeks or months with respect to the starting date.
• Type: It takes the type in which format you want to process the output.

Various “type” options:

Type	Meaning	Description
Y	Years	Calculates the number of years between the given dates.
M	Months	Calculates the number of complete months between the dates.
D	Days	Calculates the number of days between the given dates.
MD	Days excluding years and months	It ignores the month and years and gives the date difference in days only.
YD	Days excluding years	The date difference in days, ignoring years.
YM	Months excluding days and years	The “ym” returns the number of remaining months past the last full year.

Warning: If the Start_date is greater than the End_date, the result will be #NUM!.

Let us learn DATEDIF by some examples:

Difference in Days

Suppose we have the following data which shows start dates and due dates for the subscription of various platforms.

Let’s now calculate the differences between the start date and due dates in Days.

For this we are using the below formula to determine the no. of days between the dates:

=DATEDIF(B2,D2,”D”)

and copying it to other relevant cells.

We get,

The following Output

:

Here, we can clearly see that we were successful in calculating the no. of days in between the start and end dates.

Difference in Weeks

Proceeding with the same example data.

Applying the below formula in E2 and copying it till E7:

=DATEDIF(B2,D2,”D”)

We got:

Oopsie doopsie! We got something else, but this is not an error the result needs to be formatted in number to display the no. of weeks.

To Resolve this issue:

• Select all the Cells of Date Difference
• Press CTRL + 1. 
• Then click Number > Decimal places: 2. 
• Then Click on OK

And see the result,

Yay! We successfully got our desired results.

Difference in Months

Proceeding with the data set in the previous example, we will now try to calculate the no. of months between the given dates.

Applying the below formula:

=DATEDIF(B2,D2,”M”)

We get,

Again, don’t forget to format your result if you got something else. This time I have formatted with numbers having 0 decimal places.

Difference in Years

Yet again going with the above example data, this time to calculate the date difference in Years. For this to have proper results we are increasing the due-dates of our subscription for this example.

Applying the below formula:

=DATEDIF(B2,D2,”y”)

We get,

So, you can use the above examples to set up your own worksheet to work with date differences.

Hope, you liked this article.

Keep Learning!