Md. Abdullah Al Murad
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
Watch Video – 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.
=B5+C5Here, 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.
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.
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.
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.
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.
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.
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.
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.
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
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.
Read More: How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)
Similar Readings
- How to Calculate 90 Days from a Specific Date in Excel
- Subtract Dates in Excel to Get Years (7 Simple Methods)
- Count Days from Date to Today Automatically Using Excel Formula
- How to Add 6 Months to a Date in Excel (2 Easy Ways)
- Excel Formula to Calculate Number of Days Between Two Dates
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.
=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.
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.
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.
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 (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.
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.
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.
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.
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.
Related Articles
- How to Apply Excel Formula to Count Days from Date to Today
- Excel Formula for Number of Days Between Two Dates
- How to Calculate Age on a Specific Date with Formula in Excel
- Minus Number of Days or a Date from Today in Excel
- How to Count Months from Date to Today by Using Excel Formula
- How to Calculate Working Days Excluding Sundays in Excel
- How to Calculate Working Days in a Month in Excel (4 Easy Ways)
