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.


Download Practice Workbook


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.

excel formula add days to date

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.

Adding 2 Cells to Add Days to Date in Excel

Steps:

  • Firstly, enter the following formula in cell D5.
=B5+C5

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.

Final output of method 1to add days to date using Excel formula

Read More: How Do I Add 7 Days to a Date in Excel (5 Methods)


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.

Using DATE Function to add days to date using Excel formula

Steps:

  • Firstly, use the following formula in cell F5.
=DATE(B5,C5,D5+E5)

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.

Final output of method 2 to add days to date using Excel formula

Read More: Excel Formula to Calculate Number of Days Between Today and Another Date


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.

Utilizing Paste Special Feature to add days to date using Excel formula

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.

Using Paste Special Feature to add days to date using Excel formula

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.

Final output of method 3 to add days to date using Excel formula

Read More: How to Add 30 Days to a Date in Excel (7 Quick Methods)


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.
=DATE(YEAR(B5),MONTH(B5),DAY(B5)+C5)

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.

Employing Combination of YEAR, MONTH, and DAY Functions to add days to date using Excel formula

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.

Final output of method 4 to add days to date using Excel formula

Read More: How to Add Dates in Excel Automatically (2 Simple Steps)


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.

Using VBA Macro to Add Days in Excel to add days to date using Excel formula

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.

Inserting module to add days to date using Excel formula

  • 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

Code Breakdown

  • Firtstly, we initiated a sub procedure named Add_Day_To_Date.
  • After that, we declared a variable x as Range.
  • Then, we used the For Next loop for each value of x of the selected range.
  • Following that, we increase the value of x by 1 and reassigned it to x.
  • Subsequently, we terminated the For Next loop.
  • Finally, we ended the sub procedure.
  • 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.

Final output of method 5 to add days to date using Excel formula

Read More: How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)


Similar Readings


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.

How to Add Only Weekend days to Date in Excel

Steps:

  • Firstly, enter the following formula in cell E5.
=WORKDAY(C5,D5)

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.

Final output to Add Only Weekend days to Date in Excel

Read More: How to Add Weeks to a Date in Excel (4 Simple Methods)


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.
=DATE(YEAR(B5),MONTH(B5)+C5,DAY(B5))

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.

How to Add Months to Date in Excel

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.

Read More: How to Add Months to a Date in Excel (2 Ways)


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.
=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))

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

  • Following that, hit ENTER.

How to Add Years to Date in Excel

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.

Final output of method 8 to add months to date using Excel formula

Read More: How to Add 3 Years to a Date in Excel (3 Effective Ways)


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.

Adding Days to Date in Excel Not Working

Steps:

  • Firstly, use the following formula in cell D5.
=B5+C5
  • 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.
=TRIM(B5)+C5
  • Following that, press ENTER.

Using TRIM function to add days to date using Excel formula

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.

Final output of method 9 to add days to date using Excel formula

Read More: [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel


Practice Section

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

Practice section to add days to date using Excel formula


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.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo