Add days to date

In this example, the goal is to add days to a date. This is a frequent task in Excel when you need to calculate a new date by adding a specified number of days to an existing date.  Here are some examples of situations where this might be useful:

• Calculate a due date by adding a given number of days to a start date.
• Determine an expiration date by adding days to the manufacturing date.
• Schedule a follow-up appointment by adding days to the initial appointment date.
• Calculate the end date for a contract or legal agreement.

The article below provides several examples of how to set up a formula like this in Excel and notes some potential issues you may encounter.

About Excel dates

In Excel, dates are stored as serial numbers that begin with 1. For example, January 1, 1900, is the number 1, January 2, 1900, is 2, and so on. As I write this, the current date is May 28, 2024, which is stored as the serial number 45440. Because Excel dates are just numbers, you can perform arithmetic operations on dates, such as adding or subtracting days to get a new date.

Simple example

In the simplest case, you can hardcode the number of days directly into a formula. For example, with the date January 1, 2024, in cell A1, you can use a formula like this to add 14 days to the date:

=A1+14 // returns 15-Jan-2024

The result is January 15, 2024, which is the date 14 days after January 1, 2024. The formulas below the result of adding a different number of days to the date January 1, 2024, in cell A1:

=A1+1 // returns 02-Jan-2024
=A1+2 // returns 03-Jan-2024
=A1+3 // returns 04-Jan-2024
=A1+7 // returns 08-Jan-2024
=A1+14 // returns 15-Jan-2024
=A1+21 // returns 22-Jan-2024

Remember that "under the hood" Excel performs this arithmetic with large serial numbers. Because January 1, 2024, is stored as the number 45292, the actual math operation looks like this:

=45292+1 // returns 45293
=45292+2 // returns 45294
=45292+3 // returns 45295
=45292+7 // returns 45299
=45292+14 // returns 45306
=45292+21 // returns 45313

Excel then displays the large numbers as dates using a date number format.

Example with a cell reference

Of course, you can easily set up a formula to use a cell reference that contains the days to add. In the worksheet shown, we are not hardcoding days into the formula. Instead, we are picking up the days entered in column C with a cell reference. The formula in cell E5 looks like this:

=B5+C5

As the formula is copied down, the days in column C are added to the dates in column B. The resulting dates can be seen in column E.

Note: the dates in columns B and E are formatted with the custom date format "ddd, d-mmm-yyyy" to show the day of the week (e.g. Mon, Tue, etc.) along with the date.

Subtracting days from a date

As you might guess, you can subtract days from a date as well. To subtract 7 days from the date 1-Jan-2024 in cell A1, you can use a formula like this:

=A1-7

The result is Mon, 25-Dec-2023. If you use cell references for days, you will find it easier to keep the original formula based on addition (+) and enter the days as a negative number. You can see the result in the worksheet below, where the formula in cell E5 is:

=B5+C5

The original formula is the same, but the negative day numbers "subtract" days from the date in column B. This approach is more flexible since you can enter negative or positive days.

Days from today

If you want to add days to the current date, you can use the TODAY function. For example, to add 10 days, you can use this formula:

=TODAY()+10

This formula will return a date 10 days from today. Note that this formula will continue to calculate on an ongoing basis because the TODAY function will always return the current date.

Add workdays to date

There are many situations in business where you need to add a specific number of business days to a date, automatically skipping non-working days like weekends and holidays. In these scenarios, you can add days with the WORKDAY function or its more flexible sibling WORKDAY.INTL. For example, to add 5 business days to a date while excluding Saturdays and Sundays, you can use a formula like this:

=WORKDAY(A1,5) // add 5 working days

For a detailed explanation of using the WORKDAY function like this, including the option to skip holidays, see Add business days to a date.

Same date next month

If you want to add days to date and end up on the same day next month (or in 6 months) you will have trouble calculating how many days you need to add. Instead, you should use a different approach based on the EDATE function. With a valid date in cell A1, you can use EDATE to return a date 1 month, 3 months, and 6 months from the date like this:

=EDATE(A1,1) // same day in 1 month
=EDATE(A1,3) // same day in 3 months
=EDATE(A1,6) // same day in 6 months

The EDATE function can travel forward or backward in time based on the number of months provided. The EOMONTH function works in a similar way to EDATE but will always return a date at the end of a month.

Potential issues

Although adding dates in Excel is simple, there are several potential issues you may encounter.

• Date Formatting: Make sure cells that contain dates are formatted as dates. If not, Excel might display the result as a serial number. To format a cell as a date:
  • Use the keyboard shortcut Control + 1 to open "Format Cells".
  • Select "Date" from the list of formats.
  • Choose the desired date format and click "OK".
• #VALUE error: if your formula results in a #VALUE! error, it may be that Excel does not correctly understand the date you are adding days to. If Excel sees a date in cell A1 as text, a formula like =A1+1 will return #VALUE! To fix this problem, take steps to make Excel read the dates correctly.
• Negative result: If you end up with a negative date value (i.e. a negative serial number) Excel will not display the number as a date. Instead, you will see a string of hash characters like #######. To fix this problem, adjust the formula or the dates to return a positive result.
• Excel Date Limitations: Excel handles dates from January 1, 1900, to December 31, 9999. Adding days to a date that results in a date outside this range will cause Excel to display a string of hash characters like #######.