How to Add and Subtract Dates in Excel (6 Suitable Ways)

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.


Download Practice Workbook

You can download the practice workbook from the following download button.


6 Ways to Add and Subtract Dates in Excel

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.

a dataset of a company's Product Order Date:how to add or subtract dates in Excel


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 (6 Ways)


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.

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

Read More: How to Add and Subtract Columns in Excel (5 Easy Methods)


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))

Add and Subtract Months to Date in Excel

  • 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))

Read More: How to Subtract Sum of Several Cells from Fixed Number in Excel


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))

Subtract and Add Years to Date in Excel

  • 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 (3 Ways)


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.

subtracts one year, one month, and one day to a date

  • We’ll copy days from column D to column E one again by hitting Ctrl+C and clicking Paste Special…

Add and Subtract Days to Multiple Dates Using Paste Option

  • The Paste Special pop-up window will then appear.
  • We will select Add (or Subtract) under Operation and click OK.

copy days from column D to column E one again by hitting Ctrl+C and clicking Paste Special

  • 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.

Add and Subtract Days to Multiple Dates Using Paste Option

  • And finally, we got dates after adding days in column E.

go to Number Format under the Home tab and select the Short Date


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. Please visit our blog ExcelDemy for a quest for further excel expertise and if you have any questions, concerns, or suggestions, please leave them in the comments section.


Related Article

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo