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

We’ll use a dataset of a company’s Product Order Date. Columns B and C in the dataset represent Product Name and Order Date, respectively.

Method 1 – Subtract and Add Days to a Date in Excel

Steps:

We’ve added a column D with a number of days to add to the date from C.
Use the following formula in E5.

=C5+D5

Dates are in the C column.
Days are in the D column.

Hit Enter and drag down the Fill Handle to AutoFill.

Use this formula to subtract in E5.

=C5-D5

Method 2 – Add and Subtract Weeks to Date

We’ll use the following formula:

=Dates + Weeks in the Form of Days

Steps:

Column D initially has the duration in weeks.
Multiply the weeks by 7 to get the duration in days.

Get the Order Date in E column by adding the two columns in the formula below:

=C5+D5

Dates are in the C column.
Days are in the D column.

Hit Enter and drag down the Fill Handle to AutoFill.

Here’s the formula for subtraction:

=C5-D5

Method 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 column D, input the number of days you wish to add (positive number) or subtract (negative number) from a list of dates in column C.
Insert the following formula to add months to the Order Date in column C:

=DATE(YEAR(C5),MONTH(C5)+D5,DAY(C5))

Use the following formula to subtract months from the Order Date in column C:

=DATE(YEAR(C5),MONTH(C5-D5,DAY(C5))

Method 4 – Subtract and Add Years to Date in Excel

Here’s the generic formula:

=DATE(YEAR(date) + X years, MONTH(date), DAY(date))

Steps:

We will add the years in column D to the dates in column C:

=DATE(YEAR(C5)+D5,MONTH(C5),DAY(C5))

Hit Enter and drag down the Fill Handle to AutoFill.
To subtract, use the following formula:

=DATE(YEAR(C16)-D16,MONTH(C16),DAY(C16))

Method 5 – Add or Subtract Days, Months, and Years to Date

Here’s the complete formula:

=DATE(YEAR(date) + X years, MONTH(date) + X months, DAY(date) + X days)

Steps:

The following formula adds one year, one month, and one day (represented in D5, E5, and F5) to the date in cell C5:

=DATE(YEAR(C5)+D5,MONTH(C5)+E5,DAY(C5)+F5)

Hit Enter and drag down the Fill Handle to AutoFill.
The following formula subtracts from the date in cell C16:

=DATE(YEAR(C16)-D16,MONTH(C16)-E16,DAY(C16)-F16)

Method 6 – Add and Subtract Days to Multiple Dates Using Paste Options

Steps:

We will paste in column E two times to complete the adding days to dates.
Copy the dates from column C to column E.

Copy the days from column D.
Select the column E and right-click on it.
Select Paste Special.

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

The days are now added to the dates, which are shown as 5-digit numbers.
Select these 5-digit numbers to convert them to dates.
Go to Number Format under the Home tab and select the Short Date option.