If you have an Excel worksheet with multiple dates, chances are you’ll eventually need to calculate the difference between some of those dates. Maybe you want to see how many months it took you to pay off your debt or how many days it took you to lose a certain amount of weight?
Calculating the difference between dates in Excel is easy, but can get complicated depending on how you want to calculate certain values. For example, if you want to know the number of months from 2/5/2016 to 1/1/2016, should the answer be 0 or 1? Some people may say 0 because it is not a full month between dates and others may say 1 because it is another month.
In this article, I will show you how to calculate the difference between two dates to find the number of days, months and years using different formulas, depending on your preference.
Day between two days
The simplest calculation we can do is get the number of days between two dates. The benefit of calculating dates is that there’s really only one way to calculate the value, so you don’t have to worry about different formulas giving you different answers.
In my example above, I have two dates stored in cells A2 and A3. On the right you can see the difference between the two dates is 802 days. In Excel, there are always multiple ways to calculate the same value, and that’s what I did here. Let’s look at the formulas:
The first formula is just a simple subtraction of two dates, A3 – A2. Excel knows it’s a date and simply calculates the number of days between those two dates. Easy and very straightforward. Alternatively, you can also use DAY Constan.
=DAYS(A3, A2)
This function takes two arguments: an end date and a start date. If you convert the dates in the formula, you will only get a negative number. Finally, you can use a function called DATEDIFis included in Excel from Lotus 1-2-3 days, but is not an officially supported formula in Excel.
=DATEDIF(A2, A3, "D")
As you enter the formula, you will find that Excel does not give you any suggestions for fields etc. Fortunately, you can see the syntax and all supported arguments for DATEDIF . function here.
As an added bonus, what if you wanted to calculate the number of weekdays between two dates? It’s also easy because we have a built-in formula:
=NETWORKDAYS(startDate,endDate)
Calculating the number of days and days of the week is simple enough, so let’s talk about the months now.
Month between two dates
The hardest calculation is the number of months because of how you can round up or round down depending on whether it’s a complete month or a month. Now there is a MONTHS function in Excel, but it is very limited because it will only consider the month when calculating the difference, not the year. This means it is only useful to calculate the difference between two months in the same year.
Since that is nonsense, let’s look at some formulas that will give us the correct answer. Microsoft has provided the herebut if you’re too lazy to visit the links, I’ve also provided them below.
Round Up - =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)
Round Down - =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate)) *12+MONTH(LDate)-MONTH(EDate)
Now these are two pretty long and complicated formulas and you really don’t need to understand what’s going on. Here are the formulas in Excel:
Note that you should edit the formula to round down in the actual cell itself because for some weird reason the entire formula doesn’t show up in the formula bar. To see the formula in the cell itself, click Formula tab and then click Show recipe.
So what is the end result of the two formulas on our current example? Let’s see below:
Rounding up gives me 27 months and rounding down gives me 26 months, which is the exact number, depending on how you want to look at it. Finally, you can also use the DATEDIF function, but it only calculates the full months, so in our case the answer is 26.
=DATEDIF(A2, A3, "M")
Year between two dates
As well as months, years can also be calculated depending on whether you want to count 1 year as full 365 days or if a year changes. So let’s take our example where I used two different formulas to calculate the number of years:
One formula uses DATEDIF and the other uses the YEAR function. Since the difference in number of days is only 802, DATEDIF shows 2 years while YEAR function shows 3 years.
Again, this is a matter of preference and depends on what exactly you are trying to compute. You should know both methods so that you can solve different problems with different formulas.
As long as you’re careful with the formula you use, it’s fairly easy to calculate exactly what you’re looking for. There are also a lot of other date functions besides the ones I mentioned, so feel free to check those out also on Support office Location. If you have any questions, feel free to comment. Interesting!
