Suppose you want to adjust a projects schedule date by adding two weeks to see what the new completion date will be, or you want to determine how long a single activity will take to complete in a list of project tasks. You can add or subtract a number of days to or from a date by using a simple formula, or you can use worksheet functions that are designed to work specifically with dates in Excel.
Being able to calculate the difference between two dates is extremely useful in Excel. You can use date math to find durations, compare dates, track deadlines, and more.
In this step-by-step tutorial, you’ll learn different methods for subtracting dates in Excel using formulas and functions. Whether you want to find the number of days between dates or the actual resulting date, this guide covers several approaches to date subtraction that you can quickly implement in your own spreadsheets.
Why Subtract Dates in Excel?
Subtracting dates allows you to
-
Calculate the duration between two dates. For example, you can find how many days were between a project’s start and end date.
-
Compare dates to find the time elapsed. Subtracting an old date from a new date shows how much time has passed.
-
Determine deadlines based on a fixed time period. By subtracting a set number of days from a start date, you can calculate a deadline.
-
Analyze trends over time. Subtracting dates in a dataset reveals increases or decreases across days, months, or years.
-
Create date-based KPIs. By subtracting dates, you can calculate metrics like lead time, delivery time, and cycle time.
Knowing how to quickly subtract dates unlocks many helpful business insights.
Methods for Subtracting Dates
There are a few ways to subtract dates and find the time difference in Excel.
Subtract Using the DATEDIF Function
The DATEDIF function directly calculates the difference between two dates in days, months, or years. For example:
=DATEDIF(A2,B2,"D") This returns the days between the two dates.
Subtract With a Simple Formula
=B2-A2Because Excel stores dates as serial numbers, you can simply subtract one date from another in a formula to get the number of days between them.
Subtract and Format as Days
=TEXT(B2-A2,"D") This also subtracts the dates but formats the result to show only the days value.
Subtract and Format as Date
=B2-A2+0 Adding zero converts the value to a date, displaying the actual resulting date in the format you want.
Step-by-Step Guide to Subtracting Dates
Follow these steps to subtract dates in Excel:
- Enter your start dates in one column and end dates in another. For example:
| Start Date | End Date |
|---|---|
| 1/1/2023 | 4/15/2023 |
-
In another column, use the =B2-A2 formula to subtract the start date from the end date.
-
Copy this formula down to subtract all dates.
-
To get the duration, apply number formatting to show the value as a number of days.
-
To reveal the resulting date, apply date formatting.
-
Optionally, use =DATEDIF to directly get the difference in days, months, or years.
Subtracting dates is an essential Excel skill. Whether you need durations, deadlines, or general date comparisons, the above steps make it easy to calculate the difference between any two dates.
Useful Examples of Subtracting Dates
Here are some examples of subtracting dates in Excel for different business uses:
-
Find the number of days between a project’s start and end date.
-
Calculate someone’s age by subtracting their birthdate from today’s date.
-
Determine shipping time by subtracting the order date from the delivery date.
-
Find how many days remain until a deadline by subtracting today’s date.
-
Analyze month-over-month sales by subtracting last month’s date from this month’s.
-
Create a KPI for lead time by subtracting the prospect’s inquiry date from the sale date.
-
Calculate employee tenure by subtracting the hire date from the current date.
Learning how to subtract dates unlocks many helpful business insights. Follow the steps in this guide to master date subtraction in Excel.
Add or subtract days from a date
Suppose that a bill of yours is due on the second Friday of each month. You want to transfer funds to your checking account so that those funds arrive 15 calendar days before that date, so youll subtract 15 days from the due date. In the following example, youll see how to add and subtract dates by entering positive or negative numbers.
- Enter your due dates in column A.
- Enter the number of days to add or subtract in column B. You can enter a negative number to subtract days from your start date, and a positive number to add to your date.
- In cell C2, enter =A2+B2, and copy down as needed.
Add or subtract months from a date with the EDATE function
You can use the EDATE function to quickly add or subtract months from a date.
The EDATE function requires two arguments: the start date and the number of months that you want to add or subtract. To subtract months, enter a negative number as the second argument. For example, =EDATE(“9/15/19”,-5) returns 4/15/19.
- For this example, you can enter your starting dates in column A.
- Enter the number of months to add or subtract in column B. To indicate if a month should be subtracted, you can enter a minus sign (-) before the number (e.g. -1).
- Enter =EDATE(A2,B2) in cell C2, and copy down as needed. Notes:
- Depending on the format of the cells that contain the formulas that you entered, Excel might display the results as serial numbers. For example, 8-Feb-2019 might be displayed as 43504.
- Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2010 is serial number 40179 because it is 40,178 days after January 1, 1900.
- If your results appear as serial numbers, select the cells in question and continue with the following steps:
- Press Ctrl+1 to launch the Format Cells dialog, and click the Number tab.
- Under Category, click Date, select the date format you want, and then click OK. The value in each of the cells should appear as a date instead of a serial number.
How To Subtract Dates in Excel
How to calculate number of days between two dates in Excel?
Step 1: First, calculate the difference between two dates in Excel, so apply the B2 – A2 formula. Step 2: We may get the result in terms of date only but do not panic because we need to apply number formatting to see the number of days between these two days. Here, we have got the number of days as a result.
How to calculate the difference between two dates in Excel?
For example, look at the below data in an Excel worksheet. Step 1: First, calculate the difference between two dates in Excel, so apply the B2 – A2 formula. Step 2: We may get the result in terms of date only but do not panic because we need to apply number formatting to see the number of days between these two days.
How do I add or subtract days from a date in Excel?
Enter the number of days to add or subtract in column B. You can enter a negative number to subtract days from your start date, and a positive number to add to your date. In cell C2, enter =A2+B2, and copy down as needed. You can use the EDATE function to quickly add or subtract months from a date.
How to subtract a date from another date in Excel?
Excel treats months as values (1 through 12), which makes it easy to subtract an earlier date from another date as long as both dates fall in the same year. Use the MONTH function to obtain the month value for each date, and then calculate the difference between the values. Select cell D2, which is the first blank cell in the Duration column.
