If you have an Excel spreadsheet with a large number of dates, chances are you will eventually need to calculate the differences between some of those dates. Maybe you want to know 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 it can get tricky depending on how you want to calculate certain values. For example, if you want to know the number of months between 02/05/2016 and 01/15/2016, should the answer be 0 or 1? Some people may say 0 because there is not a full month between dates, while others may say 1 because it is a different 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.
The days are between two dates
The simplest calculation we can do is to get the number of days between two dates. The good thing about counting days is that there is really only one way to calculate a value, so you don’t have to worry about different formulas giving different answers.
In my example above, I have two dates stored in cells A2 and A3. On the right, you can see the 802 days difference between the two dates. Excel always has several ways to calculate the same value and that is what I did here Let’s take a look at the formulas:
The first formula is a simple subtraction of two dates, A3 – A2. Excel knows it is a date and simply calculates the number of days between those two dates. Easy and very simple. Alternatively, you can also use the DAYS function.
= DAYS (A3, A2)
This function takes two arguments: an end date and a start date. If you change the dates in the formula, you just end up with a negative number. Finally, you can use the DATEDIF function, which is included in Excel from Lotus 1-2-3 days, but is not an officially supported formula in Excel.
= DATEDIF (A2, A3, “D”)
When you enter the formula, you will see that Excel does not offer any options for fields, etc. Fortunately, you can see the syntax and all supported arguments for the DATEDIT function here
As an added bonus, what if you want to count the number of days per week between two dates? It’s easy enough because we have a built-in formula:
= NETWORKDAYS (startDate, endDate)
Counting the number of days and weekdays is easy enough, so let’s talk about months.
The most famous between two dates
The most difficult calculation is the number of months, because you can round up or down depending on whether it is a full month or an incomplete month. Excel now has the MONTHS function, but it is very limited because it only considers the month, not the year, when calculating the difference. This means that it can only be used to calculate the difference between two months of one year.
Since this is pointless, let’s look at a few formulas that will give us the correct answer. Microsoft provided them here, but if you’re too lazy to visit the link, I’ve provided them below as well.
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)
These are two rather long and complex 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 rounding formula in the cell itself, because for some strange reason the entire formula does not appear in the formula bar. To see the formula in the cell itself, click the Formulas tab and click Show Formulas.
So what is the final result of the two formulas in our current example? See below:
Rounding gives me 27 months, rounding down gives me 26 months, which is exact depending on how you want to look at it. Finally, you can also use the RAZDIF function, but it only calculates full months, so in our case it returns 26.
= RAZDIF (A2, A3, “M”)
Years between two dates
As with months, years can also be calculated depending on whether you want to count 1 year as a full 365 days or whether the year change is taken into account. So let’s take our example where I used two different formulas to calculate the number of years:
One formula uses RAZDAT, and another uses the YEAR function. Since the difference in the number of days is only 802, RAZDAT shows 2 years, while the YEAR function shows 3 years.
Again, this is a matter of preference and depends on what exactly you are trying to calculate. It is recommended to know both methods in order to solve different problems with different formulas.
If you choose the formulas you use carefully, it is fairly easy to figure out exactly what you are looking for. Besides the ones I mentioned, there are also many more functions for working with dates, so feel free to check them out on the Office support site as well. If you have any questions, do not hesitate to comment. Enjoy!
–