The Correct Way to Add Dates in Excel

You can add days to dates in Excel using the plus operator, or a SUM function. If you’re looking to add greater values such as months, or years, use the EDATE and DATE functions respectively.

Before adding your dates, make sure your cells are formatted as dates. Select your cell/range and head to the Home tab. In the Number section, ensure the format is set to either Short Date or long date.

Add Days to Date

There are two ways to add days to dates. To add a different number of days to each date, use either the plus operator or the SUM function.

If you wish to add the same number of days in an entire column, use Paste Special to save time.

Using the Plus Operator

Let’s add days in range B2:B5 to dates in their corresponding cells in range A2:A5 using the plus operator.

Here are the formula and results for each date value:

Use Paste Special

If you want to quickly add days into the range, use the Paste Special tool.

Paste Special allows you to operate on cells before you paste them to your sheet. We will use the Add operation from Paste Special to add more days to our dates in Excel.

Let’s assume I want to extend all my employee’s task deadlines by 5 days. Here’s how I’ll use the Paste Special tool to make this adjustment:

1. Select cell G5 and copy it (Ctrl + C). Cell G5 has the number of days I wish to add.
2. Select range A2:A11 and right-click on it.
   
3. Choose Paste Special.
4. Under Paste, choose Values.
5. In the Operation section, select Add.
   
6. Click OK.

Add Months to Date

Say, you’re now trying to postpone a project for a few months. While you can convert the month into days, this can take you some time. This is because months have a varying number of days of 28, 30, or 31.

You can instead use the EDATE function to add months to your total date. However, remember that the EDATE function is only for integers and not decimal values.

Here’s the syntax for EDATE when constructing a formula:

=EDATE(start_date, months)

Here is a list of ten different dates in range B2:B11. We will be extending these dates by the months listed in their corresponding cells in range C2:C11.

In cell C2, enter the following formula and apply it to the rest of the range:

=EDATE(B2,C2)

The current cell format is set to General. Therefore, we received the serial number values for the dates. To change this format, you can simply go to Home > General > Short Date/ Long Date.

Add Years to Date

You can use the above-mentioned steps to easily add years to your date in Excel.

However, if you wish to use a different method, you can use the DATE function. Here’s how you write the DATE function when constructing a formula:

=DATE(year,month,day)

If you have year, month, and days separated into three different cells, you can reference them in the allocated sections. However, if you have full date values in the cell, you will have to nest your date inside the YEAR, MONTH, and DAY functions.

Then, in the year section of the argument, simply use the plus operator and insert the number of years you wish to add to the date.

Here’s how I added years to my date in the following sheet:

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