Add or subtract dates

Here are some ways you could use a formula or worksheet functions that work with dates to do things like, finding the impact to a project’s schedule if you add two weeks, or time needed to complete a task.

Let’s say your account has a 30-day billing cycle, and you want to have the funds in your account 15 days before the March 2013 billing date. Here’s how you would do that, using a formula or function to work with dates.

  1. In cell A1, type 2/8/13.

  2. In cell B1, type =A1-15.

  3. In cell C1, type =A1+30.

  4. In cell D1, type =C1-15.

    cakculate date

Add months to a date

We’ll use the EDATE function and you’ll need the start date and the number of months you want to add. Here’s how to add 16 months to 10/24/13:

use EDATE formula to add months to a date

  1. In cell A1, type 10/24/13.

  2. In cell B1, type =EDATE(A1,16).

  3. To format your results as dates, select cell B1. Click the arrow next to Number Format, > Short Date.

Subtract months from a date

We’ll use the same EDATE function to subtract months from a date.

Type a date in Cell A1 and in cell B1, type the formula =EDATE(4/15/2013,-5).

Subtract months from a date

Here, we’re specifying the value of the start date entering a date enclosed in quotation marks.

You can also just refer to a cell that contains a date value or by using the formula =EDATE(A1,-5)for the same result.

More examples

To add years to or subtract years from a date

Adding and subtracting dates examples

Start Date

Years added or subtracted

Formula

Result

10/24/2013

3 (add 3 years)

=DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))

10/24/2016

10/24/2013

-5 (subtract 5 years)

=DATE(YEAR(A4)+B4,MONTH(A4),DAY(A4))

10/24/2008

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×