How to add and subtract a date from today in Excel

Here’s how you can add or subtract a date from today’s date in Microsoft Excel:

1. Understanding Dates in Excel

• Microsoft Excel stores dates as serial numbers, representing the date value relative to a base date.

• For instance, today’s date is represented as the serial number of the current system date.

• You can manipulate dates using worksheet functions, such as the TODAY function, DATEDIF function, EDATE function, and simple arithmetic operations.

2. Adding or Subtracting Days

To add or subtract a specific number of days to/from today’s date:

1. Enter =TODAY() in a blank cell (e.g., Cell B2) to display the current date.

2. Use simple subtraction or addition:

   • To add days: =TODAY() + [number_of_days]

   • To subtract days: =TODAY() – [number_of_days]

   • Example: =TODAY() + 30 gives a future date that is 30 days after today’s date.

   • A negative number (e.g., =TODAY() – 15) provides a past date.

3. Adding or Subtracting Months

To add or subtract a specific number of months, use the EDATE function:

• Formula: =EDATE([start_date], [number_of_months])

• Example:

  • =EDATE(TODAY(), 6) gives a future date 6 months ahead.

  • =EDATE(TODAY(), -3) calculates a negative value for a date 3 months earlier.

You may also want to see: How to calculate weighted average in Excel with percentages.

4. Adding or Subtracting Years

To work with years, use the YEAR function or EDATE:

• Formula: =DATE(YEAR(TODAY()) + [number_of_years], MONTH(TODAY()), DAY(TODAY()))

• Example:

  • =DATE(YEAR(TODAY()) + 2, MONTH(TODAY()), DAY(TODAY())) calculates the current date two years in the future.

5. Calculating Date Differences

To calculate the number of days difference between two dates:

1. Use the DATEDIF function:

   • Formula: =DATEDIF([start_date], [end_date], “d”)

   • Example: If Cell B2 contains a start date and Cell C2 contains an end date, use =DATEDIF(B2, C2, “d”) to get the total number of days.

2. For business days, use the NETWORKDAYS function:

   • Formula: =NETWORKDAYS([start_date], [end_date]).

6. Practical Examples for Worksheets

• Project timelines: Calculate a project deadline:

  • =TODAY() + 45 gives a specific date for the due date 45 days from now.

• Checking account: Use date columns to subtract past dates from the current time to find the number of days difference.

• Custom number format: Highlight date formats by using the Format Cells dialog box to apply a custom format (e.g., dd/mm/yyyy).

You may also want to see: How to apply the same formula to multiple cells in Excel.

7. Handling Negative Results

When subtracting dates, a negative result can appear:

• Example: =A1 – B1 may show ##### if the result is negative. Fix it with the ABS function:

  • Formula: =ABS([value_of_the_start_date] – [given_date]).

8. Using VBA or Power Query for Advanced Date Manipulation

• A VBA macro or Power Query can simplify date manipulation for more complex needs, such as filling missing dates in a list of dates or converting text strings to valid dates.

9. Common Issues

• Date format errors: Ensure all cells use the same date data type.

• Column alignment: Use a single column for worksheet dates like column B or cell D.

• Extra cost: Avoid overwriting data by using paste values after applying formulas.

10. Examples of Formulas

1. Add 10 days: =TODAY() + 10

2. Subtract 5 days: =TODAY() – 5

3. Add 3 months: =EDATE(TODAY(), 3)

4. Calculate number of months: =DATEDIF(B1, C1, “m”)

5. Find number of business days: =NETWORKDAYS(TODAY(), B2)

These formulas allow you to handle date columns, calculate differences, and keep track of project deadlines or specific dates.

You may also want to see: How to add specific cells in excel (summing cells).