While continuing to work with a dataset, sometimes users need to know how to add or subtract dates in Excel. You can simply Add and Subtract Dates in Excel. In this article, I’ll demonstrate to you six simple ways to add and subtract dates in Excel. I hope you will find involvement in these methodologies and may enhance your skill by exercising the methods.
How to Add and Subtract Dates in Excel: 6 Ways
Now we’ll explore several instances using a dataset of a company’s Product Order Date. Columns B and C in the dataset represent Product Name and Order Date, respectively. The cells in the dataset span from B4 to C10. Without further ado, let us begin by following the stages of each approach. I’ll provide the required visuals to help you comprehend.
1. Subtract and Add Days to Date in Excel
In some cases, you have a date in a cell or a set of dates in a column, you may use arithmetic operations to add or subtract a specified day from all those dates.
📌 Steps:
- There are numerous methods to get the Order Date. First of all, we will use the following formula:
=C5+D5
- Dates are in the C column.
- Days are in the D column.
- Here is our final dataset with added new dates.
- Repeat the mathematical procedure to subtract a certain number of days from a given date. The only difference between this example and the last one is the negative symbol instead of the plus sign.
=C5-D5
Read More: How to Add and Subtract in One Cell in Excel
2. Add and Subtract Weeks to Date
If you wish to add or subtract complete weeks to a specific date, follow the same methods as for adding or subtracting days, but multiply the number of weeks by 7.
=Dates + Weeks in the Form of Days
📌 Steps:
- We will add 1 to 6 weeks to the Order Date column in C. But first of all, we have to multiply the weeks by 7.
- There are several ways to obtain the Order Date in E column by adding two columns like the formula below:
=C5+D5
- Dates are in the C column.
- Days are in the D column.
- Finally, here is our completed dataset, complete with revised dates.
- Repeat the technique to deduct a certain number of days from a specified date. The only difference between this and the last example is that the negative symbol is used instead of the plus sign.
=C5-D5
3. Add and Subtract Months to Date in Excel
You may use the DATE, YEAR, MONTH, and DAY functions to add or subtract a certain number of full months from a date, as seen below.
=DATE(YEAR(date), MONTH(date) + X months, DAY(date))
📌 Steps:
- In any cell, say D, input the number of days you wish to add (positive number) or subtract (negative number) from a list of dates in column C.
- Now, insert the following formula to add months to the Order Date in column C:
=DATE(YEAR(C5),MONTH(C5)+D5,DAY(C5))
- Similar to the previous method, insert the following formula to add months to the Order Date in column C:
=DATE(YEAR(C5),MONTH(C5-D5,DAY(C5))
Similar Readings
4. Subtract and Add Years to Date in Excel
In Excel, adding years to date is comparable to adding months. You use the DATE(year, month, day) method once more, however this moment you provide the number of years to add:
=DATE(YEAR(date) + X years, MONTH(date), DAY(date))
📌 Steps:
- We will add the years in column D to the dates in column C. The formulae in your Excel spreadsheet may look like this:
=DATE(YEAR(C5)+D5,MONTH(C5),DAY(C5))
- Now drag the fill handle down to complete the series.
- To subtract, use the following formula.
=DATE(YEAR(C16)-D16,MONTH(C16),DAY(C16))
5. Add or Subtract Days, Months, and Years to Date
If you paid close attention to the preceding examples, you should have guessed how to add or subtract a mix of years, months, and days from a date in a single formula. We shall now integrate all three approaches.
=DATE(YEAR(date) + X years, MONTH(date) + X months, DAY(date) + X days)
📌 Steps:
- For instance, the following formula adds one year, one month, and one day to date in cell C5:
=DATE(YEAR(C5)+D5,MONTH(C5)+E5,DAY(C5)+F5)
- Similar to the previous fashion, we will fill the rest of the cells by dragging down the fill handle.
- For instance, the following formula subtracts one year, one month, and one day to date in cell C16:
=DATE(YEAR(C16)-D16,MONTH(C16)-E16,DAY(C16)-F16)
Read More: How to Add or Subtract Based on Cell Value in Excel
6. Add and Subtract Days to Multiple Dates Using Paste Option
Some Excel users prefer to add or subtract days manually without using formulae. I’ll show you how to use Excel’s Paste Special tool to add or subtract the same amount of days from various dates.
📌 Steps:
- We will paste in column E two times to complete the adding days to dates.
- First, we will copy dates from column C to column E by pressing Ctrl+C and then Ctrl+V.
- We’ll copy days from column D to column E one again by hitting Ctrl+C and clicking Paste Special…
- The Paste Special pop-up window will then appear.
- We will select Add (or Subtract) under Operation and click OK.
- The days are now added to the dates, which are shown as 5-digit numbers.
- We will select these 5-digit numbers to convert them to dates.
- To do so, we will go to Number Format under the Home tab and select the Short Date option.
- And finally, we got dates after adding days in column E.
Download Practice Workbook
You can download the practice workbook from the following download button.
Conclusion
Follow these steps and stages of the topic of how to add or subtract dates in Excel. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section.