How to Calculate Number of Days Between Two Dates in Excel

In a previous post, I showed you how to use the NETWORKDAYS() function in Excel to calculate the number of work days between two dates in Excel. In today’s post we’ll take a look at how you can use further Excel functions to calculate number of days between two dates in Excel. We’ll also see how to calculate the number of weeks, months and years between two dates in Excel also.

For the purpose of doing this computation, we make use of the DATEDIF function. This function is actually an unlisted function in Excel, so if you were to search for the function in your Insert Function dialog, you won’t find it. Nevertheless, its an easy function to use once you know its arguments.

DATEDIF Parameters

For the purpose of computing the difference in days, weeks, months, or years, you need the start date, the end date and the parameter that tells Excel what you want to extract. So, here are the different parameters:

m = months. It extracts the whole calendar months between the specified dates.

d = days. It extracts the number of days between the specified dates.

y = years. It extracts the whole calendar years between the specified dates.

ym = months excluding years. It extracts the whole calendar months between the specified dates treating them as if they were from the same year.

yd = days excluding years. It extracts the whole calendar days between the dates treating them as if they were from the same year.

md = days excluding years and months. It extracts the whole calendar days between the dates treating them as if they were from the same month and year.

Let’s get started, shall we?

How to Calculate Number of Days Between Two Dates in Excel

In cell A2, we have a Start date 2-Oct-78 and in cell B2 we have an end date 27-Oct-16. To calculate the number of days between the two days, we can simply make use of the subtraction formula to get the result.

So in cell A3, if I compute End date minus Start date. I’ll get the difference in days. So my formula will be =B2-A2 which gives the result of 13905 days.

I can also use the DATEDIF function to get the result.

In cell A3 type =DATEDIF(A2,B2,”d”) which also gives the result of 13905 days.

How to Calculate Number of Weeks Between Two Dates in Excel

Computing the number of weeks between two days requires a different approach. Here, you want to subtract the end date from the start date and divide it by 7 ( number of days in the week) to get the net result.

In cell D2, type =(B2-A2)/7 which gives the result of 1986.428571 weeks.

If you want only whole weeks, you can use the INT function. In this case, the formula will read =INT((B2-A2)/7)

How to Calculate Number of Months Between Two Dates in Excel

Using the same dates as in the previous example, let’s compute the number of months between two dates in Excel. For this we can again used the DATEDIF formula with the parameter “m”

In cell E2, we enter the function =DATEDIF(A2,B2,”m”) which will give the result of 456 weeks.

How to Calculate Number of Years Between Two Dates in Excel

To calculate the number of years between two dates in Excel, we can use the YEAR function, which returns the year of the date. We extract the year of the first date and the year of the second date and subtract the two to get the difference in years.

In cell F2, we enter the formula =YEAR(B2)-YEAR(A2)

This will return the value in years, which is 38.

You can also use the DATEIF function for this.

In cell F2, type =DATEDIF(A2,B2,”y”) which will give the result of 38 weeks.

One point to remember when using the DATEDIF function is that it returns only the number of complete days, months or years. This may sometimes give inaccurate results especially when the day/month number of the second date is lower than the day/month number of the first date. So use with caution.

If you found this tutorial helpful, be sure to read all our tutorials in the Learn Excel series. If you want to learn Excel from scratch, consider taking up our Udemy course, Excel 2016 for Beginners, which is available at a nominal price of just $10.