How to Add or Subtract Dates in Excel

Adding or subtracting days, months, or years from a date is a common task in Excel, especially for project management, financial planning, or scheduling. Whether you need to adjust a project timeline, calculate due dates, or determine billing cycles, Excel provides simple formulas and functions to handle date arithmetic efficiently.

Add or Subtract Days from a Date

If you need to adjust a date by a specific number of days, you can use a basic arithmetic formula. For example, suppose you want to ensure funds arrive in your account 15 days before a due date. Here’s how to do it:

1. Enter your due dates in column A.
2. Enter the number of days to add or subtract in column B. Use a negative number to subtract days and a positive number to add days.
3. In cell C2, enter the formula:
   =A2+B2
   Copy this formula down the column as needed.

Add or Subtract Months from a Date with the EDATE Function

The EDATE function is designed specifically for adding or subtracting months from a date. It requires two arguments: the start date and the number of months to add or subtract. To subtract months, use a negative number.

1. Enter your starting dates in column A.
2. Enter the number of months to add or subtract in column B. Use a minus sign (-) to indicate subtraction (e.g., -1).
3. In cell C2, enter the formula:
   =EDATE(A2,B2)
   Copy this formula down the column as needed.

Formatting Dates Correctly

Excel stores dates as sequential serial numbers. If your results appear as numbers (e.g., 43504 instead of 8-Feb-2019), follow these steps to format them as dates:

1. Select the cells with the serial numbers.
2. Press Ctrl+1 to open the Format Cells dialog.
3. Under the Number tab, select Date and choose your preferred date format.
4. Click OK to apply the changes.

Add or Subtract Years from a Date

To add or subtract years from a date, use the DATE function combined with YEAR, MONTH, and DAY functions. For example, to add 3 years to a date in cell A2:

1. Enter your starting dates in column A.
2. Enter the number of years to add or subtract in column B.
3. In cell C2, enter the formula:
   =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))
   Copy this formula down the column as needed.

How the Formula Works

• The YEAR function extracts the year from the date in A2 and adds the value from B2.
• The MONTH and DAY functions retain the original month and day from A2.
• The DATE function combines these values into a new date.

Add or Subtract a Combination of Days, Months, and Years

To adjust a date by a combination of days, months, and years, use the DATE function with nested arithmetic. For example:

1. Enter your starting dates in column A.
2. Enter the number of years to add or subtract in column B.
3. Enter the number of months to add or subtract in column C.
4. Enter the number of days to add or subtract in column D.
5. In cell E2, enter the formula:
   =DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)+D2)
   Copy this formula down the column as needed.

How the Formula Works

• The YEAR function extracts the year from A2 and adds the value from B2.
• The MONTH function extracts the month from A2 and adds the value from C2. If the result exceeds 12, Excel automatically adjusts the year.
• The DAY function extracts the day from A2 and adds the value from D2. If the result exceeds the number of days in the month, Excel adjusts the month and year accordingly.
• The DATE function combines these values into a new date.

Practical Examples

Example 1: Adjusting Billing Cycles

Suppose your account has a 30-day billing cycle, and you want to ensure funds are available 15 days before the billing date in March 2013.

1. In cell A1, enter the billing date: 2/8/13.
2. In cell B1, enter the formula to subtract 15 days:
   =A1-15
3. In cell C1, enter the formula to add 30 days:
   =A1+30
4. In cell D1, enter the formula to subtract 15 days from the new date:
   =C1-15

Example 2: Adding Months to a Date

To add 16 months to the date 10/24/13, use the EDATE function:

1. In cell A1, enter the start date: 10/24/13.
2. In cell B1, enter the formula:
   =EDATE(A1,16)
3. Format the result as a date by selecting Short Date from the Number Format menu.

Example 3: Subtracting Months from a Date

To subtract 5 months from the date 4/15/2013, use the EDATE function:

1. In cell A1, enter the start date: 4/15/2013.
2. In cell B1, enter the formula:
   =EDATE(A1,-5)
   Alternatively, you can directly enter the date in the formula:
   =EDATE("4/15/2013",-5)

Example 4: Adding or Subtracting Years

To add or subtract years from a date, use the DATE function as shown below: