If you've ever found yourself trying to figure out how to subtract dates in Excel to get days, months, or years, you're not alone. Excel can be a powerful tool, but it can also be a bit tricky when it comes to working with dates. Fear not, I have a few different ways that will demystify the process for you! In this article, we'll walk through the easy ways to perform date subtraction, to easily determine days (or months or years) between specific dates.
First, why would you need to count days between dates? There are many reasons to calculate days between existing dates. It could be to determine how many days are left before the due date of a school or work assignment. The winter countdown for how many days until your Mexican vacation (arriba!). Perhaps you want to know how many years until you can retire? (Psst, you could actually leave that 9 to 5 job by doing Excel Freelance work and selling templates). That's only a few reasons! There are many reasons why you would want know this.
Table of Contents
How to Subtract Dates in Excel to Get Days, Months, or Years
The easiest way to calculate days between dates is using a simple formula like subtraction formulas. This is taking the end date minus the starting date. So, let's say you want to know how many days between April 21, 2020 and June 30, 2023. Use the following formula:
=end_date – start_date
=B2 – A2
Using the DATEDIF Function,
Another simple way to find the difference between two dates in Microsoft Excel is by using the DATEDIF function. Now, you might wonder, “Hey, why is ‘DATEDIF' not a typo of ‘date if'?” Trust me, many have been puzzled by this, but it's one of those quirky Excel things we just have to embrace. To use the DATEDIF function, you need three arguments: start date, end date, and the unit of measurement you want the result in.
Here's the syntax: =DATEDIF(start_date, end_date, unit)
The following table was taken from the Support Microsoft 365 website and determines what to use for the third argument (unit argument) based on what information you want to be returned:
Units | Returns |
“Y” | The number of years (full years, as in no partial years – no decimals) within the period |
“M” | The number of months (full years, as in no partial months – no decimals) within the period |
“D” | The total number of days within the period |
“MD” | The difference between the days in the start date and the end date. The months and years in the dates are ignored. This one is noted to have limitations that can give incorrect results, therefore I will not include more information on it. Microsoft does not recommend using this argument. I will discuss an alternative in the next section. |
“YM” | The difference between the months in the start date and end date. The years and days of the dates are ignored. |
“YD” | The difference between the days of start date and end date. The years of the dates are ignored. |
Let's say you have the starting date in cell A2 and the end date, the current date, in cell B2. To calculate the number of days between these two dates, you'd use the formula:
=DATEDIF(A2, B2, “D”)
This will give you the result in days! Remember, Excel loves those double quotation marks around the “d” to indicate days.
Using the previous example, you can determine that:
- There are 3 years (number of complete years) between 2020 and 2023 (“Y”)
- There are 38 months between April 2020 and June 2023 (“M”)
- There are 1165 days between April 21, 2020 and June 30, 2023 (“D”)
- There are 2 months between April and June (“YM”)
- There are 70 days between April 21 and June 30 (“YD”)
If you change the dates will the table automatically update based on the new list of dates? Absolutely! You can change the start or end dates with a new date and day functions will recalculate to determine the number of days between the new dates. The resulting value for months and years will only change if the number of complete months or years has increased or decreased.
Using the DAYS Function
Another way to determine the days between different dates is the DAYS function, which looks like this:
= DAYS (end_date, start_date)
Let's put this into action! If you want to calculate the number of days between the starting date (cell A2) and the ending date (cell B2), the above formula will look like this:
= DAYS (B2, A2)
Keep in mind that if you get a negative number it probably means that you have the start date and end date backwards in your formula. You want to have your earlier date as the second argument (in this case, the start date) in order for a positive number to be shown.
Pretty straightforward, right? But wait, there's more!
Using the NETWORKDAYS Function
What if you want to know how many working days are between January 1st and June 30 when your kids start summer vacation? To find this information, you can use the NETWORKDAYS function (also known as the workday function) to calculate weekdays only. The function will remove weekend days and holidays (you must tell the formula how many holidays exist between the two dates, so job down a list of holidays from your calendar and count them).
Here's the syntax: =NETWORKDAYS(start_date, end_date, [holidays])
To add [Holidays], you would have a list of the holiday dates or date serial numbers that you would reference, but we'll talk more about this in How to Convert Serial Numbers to Dates in Excel. So for now we'll just use zero for no holidays.
=NETWORKDAYS(A2, B2, 0)
That's it! The countdown begins! The final result is 130 working days until the kids start holidays.
Frequently Asked Questions
Can I Subtract Dates with Different Formats in Excel?
Yes, you can! Excel is quite flexible when it comes to date formats. As long as Excel recognizes the data in the cells as dates, you can subtract them without issue. So, whether it's “MM/DD/YYYY,” “DD/MM/YYYY,” or any other format, you're good to go!
Alternatively, you can also use the DATEDIF function that allows you to find days, months, or years (these being referred to as ‘units' within the function) between dates.
The syntax is: =DATEDIF(start_date, end_date, unit)
How Do I Account for Business Days Instead of Calendar Days?
Great question! While DATEDIF and the other functions we've discussed count all days between two dates, including weekends, you might want to consider only business days. Excel does have a way to remove weekend days though and that's the NETWORKDAYS function. This function calculates the working days between two dates, excluding weekends and specified holidays.
The syntax is: =NETWORKDAYS(start_date, end_date, [holidays])
Can I Subtract Dates Using the “Today” Date in Excel?
Absolutely! If you want to find the difference between a date in the past and today's date, you can use the TODAY function to subtract a date from today.
That's It!
As you can see, subtracting dates in Excel to get days, months, or years, is pretty easy once you learn a few functions. Of course, like anything in Excel, there are a number of ways to do everything so the list above isn't comprehensive, but they are the easiest ways.
What is the best way to subtract dates in Excel to find days? It depends on what other criteria you are including. If you need to remove weekends and holidays, well the NETWORKDAYS function is your best bet. Just need to calculate how many days in total are between dates. The DATEDIF function will work easily.
Remember, practice makes perfect! The more you experiment with dates and Excel's different functions, the more proficient you'll become. So, go forth and conquer those date calculations with flair! Happy counting!
Hi! I'm Lindsay!
My nearly 20 years of experience working in various office settings have made me intimately familiar with the power and versatility of Microsoft Excel and I’m here to empower individuals who may not have had the opportunity to gain hands-on experience with Excel through traditional means.
Follow me as I share basic Excel tips and tricks, and answer common questions to master the essentials of Excel.