How to Add Days to Date Using Excel Formula (5 Easy Ways)

We often need to establish a routine or timetable in order to better organize projects and manage time. While making time schedules, MS Excel provides diverse ways to add days to date. In this article, we’re going to show 5 simple methods to add days to date using Excel formula.

Date Basics in Excel

If we need to add dates to Excel, we can enter the numbers and format them into different dates. Excel’s default date format is m/dd/yyyy (6/20/2021). We may tailor it to our specific requirements. By following the methods outlined below, we can modify the date format.

• Firstly, select the cells of the Long Dates column.
• Then go to the Home tab from Ribbon.
• After that, click on the drop-down icon marked in the following image.
• Finally, choose the Long Date option from the drop-down.

Consequently, the Long Date format will be applied as shown in the image below.

5 Simple Methods to Add Days to Date Using Excel Formula

In this section of the article, we will learn 5 simple methods to add days to date using Excel formula.

Not to mention that we have used the Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

1. Adding 2 Cells to Add Days to Date in Excel

Adding two cells is one of the easiest ways to add days to date using Excel Formula. Let’s say we need to update the Promotion Dates of several employees of a company. Let’s follow the steps outlined below to do this.

Steps:

• Firstly, enter the following formula in cell D5.

Here, cell B5 represents the cells of the Dates column, and cell C5 refers to the cells of the Days (+/-) column.

• Following that, press ENTER.

As a result, you will have the updated Promotion Dates in cell D5.

• Now, by using the AutoFill option of Excel, you can get the remaining Promotion Dates as demonstrated in the following picture.

2. Using DATE Function

Using the DATE function is another smart way to add days to date using Excel formula. The DATE function returns a specific date while the Year, Month, and Day columns are provided.

Let’s say, we have a table of “Project Deadlines” as our dataset. Our goal is to add days to date using the DATE function. Let’s use the instructions outlined below.

Steps:

• Firstly, use the following formula in cell F5.

Here, cell B5 indicates the Year, cell C5 represents the Month, cell D5 refers to the Day, and cell E5 indicates the number of days that are to be added.

• After that, hit ENTER.

Subsequently, you will have the following output in cell F5.

• Finally, use the AutoFill option of Excel to get the remaining outputs in the Updated Dates column.

3. Utilizing Paste Special Feature of Excel

By using the Paste Special feature, we can easily add days to date in Excel. It is a simple yet quite efficient way to add days to date in Excel.

For instance, we have “New Promotion Dates” as our dataset. Our goal is to find the updated Promotion Dates by adding 10 days. Let’s follow the procedure mentioned in the following section to do this.

Steps:

• Firstly, select the cells of the Dates column and use the keyboard shortcut CTRL + C to copy the cells.

• After that, click on cell D5 and press CTRL + V to paste the copied cells in the Promotion Dates column.

• Now, select cell C5 and press CTRL + C.

• Following that, select the cells of the Promotion Dates column.
• Then, go to the Home tab from the Ribbon.
• Now, select the Paste option.
• Next, choose the Paste Special option from the drop-down.

As a result, the Paste Special dialogue box will be available on your worksheet.

• Now, in the Paste Special dialogue box, choose the Values option under the Paste section.
• After that, select the Add option under the Operation section.
• Finally, click OK.

Consequently, you will see that 10 days are added in the Promotion Dates column, as shown in the following image.

4. Employing Combination of YEAR, MONTH, and DAY Functions

One of the many ways to add days to a date in Excel is to use a combination of the YEAR, MONTH, and DAY functions. The YEAR function returns the year of a specified date. The MONTH function gives the number of the month of a date as output. The DAY function gives us the number of days of a specified date. Now, let’s follow the instructions outlined below.

Steps:

• Firstly, use the following formula in cell D5.

Here, cell B5 represents the cell of the Dates column, and cell C5 refers to the number of days that need to be added.

• After that, press ENTER.

As a result, the specified number of days will be added and you will have the output in cell D5.

• Finally, by using the AutoFill option, you will have the remaining outputs as demonstrated in the following image.

5. Using VBA Macro to Add Days in Excel

Using the VBA Macro option is another smart way to add days to date in Excel. Here, we will use a VBA code to automatically add 1 day to a selected range of dates. Let’s follow the steps mentioned below to do this.

Steps:

• Firstly, go to the Developer tab from the Excel Ribbon.
• After that, click on the Visual Basic option from the Code group.

As a result, the Microsoft Visual Basic window will open on your worksheet as shown in the following picture.

• Now, go to the Insert tab in the Microsoft Visual Basic window.
• Then, select the Module option from the drop-down.

• Following that, write the code given below in the blank Module.

Sub Add_Day_To_Date()

Dim x As Range

For Each x In Selection.Cells
x.Value = x.Value + 1
Next x

End Sub

• After writing the code, click on the Save icon as marked in the image given below.

• Next, press the keyboard shortcut ALT + F11 and you will be redirected to the worksheet.
• Subsequently, copy and paste the cells of the Dates column into the Updated Dates column.

• Following that, select the cells of the Updated Dates column and go to the Developer tab.
• Then, click on the Macros option from the Code group.

• Now, in the Macro dialogue box, click on Run.

As a result, 1 day is added to your selected cells, as shown in the image below.

How to Add Only Weekends to Date

In our daily lives, we often need to edit the deadline dates of various projects. Sometimes we need to add weekends to the date. By using Excel, we can achieve this task quite easily. To do this, we will use the WORKDAY function of Excel. The WORKDAY function gives us the serial number of days of a week based on a specified date. Now, let’s follow the steps mentioned below. For instance, we have a dataset named “Project Deadline Update“. Our aim is to find New Deadlines after adding the Weekends.

Steps:

• Firstly, enter the following formula in cell E5.

Here, cell C5 refers to the cell of the Deadlines column, and cell D5 indicates the cell of the Weekends column.

• After that, press ENTER.

As a result, you will have the following output in cell E5.

• Then, use the AutoFill option of Excel to obtain the remaining outputs.

Read More: How to Add Days to A Date in Excel Excluding Weekends

How to Add Months (e.g. 6 Months) to Date in Excel

In this section of the article, we will learn the steps to add months to date in Excel. Let’s follow the procedure discussed in the following section to do this.

Steps:

• Firstly, use the formula given below in cell D5.

Here, cell B5 indicates the cell of the Dates column, and cell C5 refers to the number of Months that need to be added.

• After that, press ENTER.

Subsequently, you will have the date with Months added in cell D5.

Finally, use the AutoFill option to get the rest of the outputs in the Promotion Dates column.

How to Add Years (e.g. 3 Years) to Date in Excel

We can also add years to date in Excel by following some simple steps. Let’s explore these steps in the following section of the article.

Steps:

• Firstly, enter the following formula in cell D5.

Here, cell C5 refers to the number of Years that need to be added.

• Following that, hit ENTER.

Consequently, 3 years will be added to the Date of cell B5, as shown in the following image.

• Now, you can use the AutoFill option of Excel to get the rest of the outputs.

Read More: How to Add 3 Years to a Date in Excel

What to Do If Adding Days to Date in Excel Is Not Working

When working in Excel, we often run across the problem of not being able to add days to a date. Most of the time, this occurs because a space was accidentally put at the beginning of the date. Assume we have some Dates with a space before them. Our goal is to eliminate the space and appropriately add the number of days to the date. Let’s achieve this by following the steps mentioned below.

Steps:

• Firstly, use the following formula in cell D5.

• Now, press ENTER.

You can see that it returns a #Value Error!

To fix this issue, we will use the TRIM function of Excel. The TRIM function removes the extra spaces from a string.

• Enter the following formula in cell D5.

• Following that, press ENTER.

Subsequently, you will have the specified number of days added to the Date as shown in the following picture.

• Finally, use the AutoFill option of Excel to obtain the remaining outputs in the Promotion Dates column.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Download Practice Workbook

Conclusion

So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to  add days to date using Excel formula. If you have any questions, suggestions, or feedback related to this article you can comment below. You can also have a look at our other useful articles on Excel functions & formulas on our website ExcelDemy.

Adding Days to Dates: Knowledge Hub

<< Go Back to Calculate Dates | Date-Time in Excel | Learn Excel