How to Add or Subtract Dates in Excel

New to Excel and seeking a way to add or subtract dates, then this guide on how to add or subtract dates in Excel will expalin you the right way to manage project timelines, schedules, or due dates. We’ll break down simple steps to add or subtract dates using easy formulas and time-saving tips.

How to Subtract Dates in Excel

If you have two dates in cells, for example in B2 and C2, and you want to know how many days are there between them, Excel has ways to help you find the days between two dates.

Subtract Date from Other Date Directly

In Excel, each date is actually stored as a special number. The number 1 represents March 31, 2000. So, to find the difference between two dates, you can just subtract one number from the other:

'=C2-B2'

Using the DATEDIF Function

If you prefer a more advanced method, you can use the DATEDIF Function:

This function calculates the difference in days between the two dates. Just keep in mind that it won't work if start date B2 is later than the end date (C2).

'=DATEDIF(B2, C2 "d")'

Subtracting from Today's Date

To find the difference between a dates in the past and today, you can use similar formulas. Replace one of the dates with the 'TODAY()' function.

'=TODAY()-B2' Or '=DATEDIF(A2, TODAY(),"d") '

Note: This function does not work if the date you're subtracting from is later than today.

Example: Using the DATE Function

If you want to input the dates directly into the formula, you can use the 'DATE(year, month,day)' function. This formula subtracts 15 August 2000 from 20th May 2023.

=DATE(2000, 8, 20) - DATE(2023, 5, 15)

In general, if you're just looking to find the difference between two dates, the direct subtraction method is the simplest and most straightforward.

For complex calculations, like finding the difference in months or years, the DATEDIF function can be handy.

How to Subtract or Add Days to Date in Excel

Whether you're working with a single date or a list of dates, Excel makes it easy to add or subtract a specific number of day using basic arithmetic operations.

Adding Days to a Date in Excel

To add a certain number of days to a date, follow this general formula:

DATE+ N days

Below are some ways to input the date:

• Use a cell reference, e.g., '=A2 + 10'
• Utilize the 'DATE(year, month, day)' function e.g., '=DATE(2015, 5, 6) +10 '
• Leverage a function result. For example, to add days to the current date, use the 'TODAY()' Function: ='TODAY() +10'

Subtracting Days form a Date in Excel

Subtracting days form a date is just as straightforward. It's essentially the same process as addition, but with the minus sign:

Date - N days

Here are examples of how to do this:

• '=A2 -10'
• '=DATE(2015, 5, 6) - 10'
• '=TODAY() -10'

How to Subtract Weeks to Date

When you need to adjust a date by specific number of weeks, Excel offers a simple solution. You can build upon the formulas used for adding or subtracting days, with a small modification: Just multiply the number of weeks by 7. This straightforward approach allows you to seamlessly workwith weeks in your date calculations.

=Cell - N week*7

How to Subtarct Months to Date in Excel

If you want to add or subtract a certain number of whole months to a date, you can use the DATE or EDATE function:

Example : Subtract months to a date with Excel EDATE

Excel provides a specialized function called EDATE that simplifies the process of working with dates when adding or subtracting months. This function is available in all versions of Excel form 2007:

=EDATE( start_date, months)

Arguments:

Start_date - the start date from which to count the numbers of months.

Months- the number of months to add( a positive value) or subtract (a negative value).

The following formula used on our column of dates yields exactly the same result as the DATE function in the previous example:

How to Subtract Years to date in Excel

Expanding on the concept of adding months, adjusting dates by years in Excel employs a similar approach. The DATE(year, month, day)function remains your ally, with the added step of specifying the number of years to add or subtract:

'DATE(YEAR(date) -N years, MONTH(date), DAY(date))'

Example: Subtracting Years from a Date in Excel:

'=DATE(YEAR(A2) - 5, MONTH(A2), DAY(A2))'

This formula subtracts 5 years from the date in cell A2.

Alternatively, you can establish a universal formula by entering the number of years to add(positive number) or subtract (negative number) in a specified cell. Then, reference within the DATE function.

How to subtract dates to find the difference in months or years?

You can use DATEDIF function to find the difference in months or years between two dates. The syntax is: '=DATEDIF(start_date,end_date,"m") for months and '=DATEDIF(start_date, end_date,"y")' for years.

How to create a formula to subtract or add a dynamic number of days, months, or years to a date?

You can use cell reference to store the number of days, months, or years you want to add or subtract, and then incorporate these references into your formulas. This allow for dynamic adjustments based on changing values in those cells.

How to format the result of date subtraction to display as a specific date format?

After subtracting dates, the result may appear as a serial number. To format it as a date, select the cell(s), right click, choose "Format cells", and select the desired data format under the Number tab.