If you want to learn how to add days to a date in Excel, you’ve come to the right place. Keep reading!
Do you ever find yourself scrambling to meet deadlines? It can be tough keeping track of all those dates, especially when you’re juggling multiple tasks.
But fear not! If you’ve got Microsoft Excel on your computer, I’ve got a few handy tricks to share with you. Instead of stressing about counting days on a calendar, you can use Excel to make your life easier. Let me walk you through how to add days to an Excel date using several methods for different situations below.
Add Days to a Date Using the Addition Operator
This is the basic way to add a given number of days to a date to get a future date, which might be a task deadline, personal event, professional event, school exam, and so on.
Go to your worksheet that contains the input dataset. It must contain the start dates and days to be added to the start dates. There, create a new column header named Deadline or something else that suits your dataset.
Now, you need to use the addition operator to add the start date and day values of the first cell in the dataset. You can use this formula:
Hit Enter to get the output in the selected cell.
Simply drag the fill handle from the first cell downwards along the column until you reach the cell where the adjacent columns display the start date and the number of days to be added.
Excel will populate the resulting values by copying and pasting the first formula in all subsequent cells down the line.
📒 Read More: 9 Ways to Change Date Format in Microsoft Excel
Add Days to an Excel Date Using Paste Special
Paste Special lets you add the given days to dates directly on the dates column without creating an additional Deadline column.
Suppose, you’ve got a Start Date and ETA in Days column in your dataset. You need to get deadlines for each task.
Change the Start Date column header to Deadline.
Now, copy the content of the ETA in Days column by highlighting its data and pressing Ctrl + C on the keyboard.
Highlight the Deadline columns data and press Ctrl + Alt + V to open the Paste Special dialog.
On Paste Special, select Values under the Paste section and Add under the Operation section. Click OK to perform the addition action.
Excel will add and replace the existing dates in the Deadline column with the new dates after adding the given days in the ETA in Days column.
Add Days to the Current Date in Excel
Suppose, you’ve rented a movie on an online movie streaming app today. You’ve got 19 days to start watching it. If you want to find the deadline by which date you must watch the movie from the current date, you can use this method.
Open an Excel worksheet and double-click on any empty cell to enable editing.
Now, copy and paste the following formula into that cell and hit Enter:
Excel will add the given days to today’s date which is the deadline for you to watch the movie.
Add Days to an Excel Date Using the Fill Series Tool
Suppose you’ve got a small business that delivers fresh produce to local customers periodically. One of the subscription plans is delivery every 9 days for 5 times. Now, there are many customers in this subscription tier.
The problem you’ll be solving here is adding 9 days to the start date of the delivery and then adding 9 days for up to 5 deliveries. Sounds complicated? Yes, but not that much if you use Excel. Find below how can you manage the deliveries in an Excel worksheet:
Create an Excel worksheet as shown above and populate the start dates of the deliveries in the 1st Delivery column for each customer.
Now, highlight all the delivery dates in column C. Use the fill handle and drag it until the 5th Delivery column. Ignore the results created by Excel at this point.
Then, click the Fill drop-down arrow inside the Editing block of the Home tab. Click Series in the context menu to open the Series dialog box.
On the Series dialog, select Rows under Series in, Date under Type, and Day under Date unit. Also, enter numerical 9 in the Step value field. Click OK to execute the Fill Series command.
That’s it! You’ve successfully added 9 days to various start dates in Excel in a few clicks. Customize the values according to your own use case scenario.
Add Days to an Excel Date Using the TEXT Function
Suppose, your client prefers a different date format, like YYYY-MM-DD, in their business operations. If you’re sending a report to them that requires adding days to given dates, the TEXT function allows you to add days along with formatting the date entries.
If there’s a Start Date or similar column in your worksheet, highlight all of the cells that contain data and press Ctrl + 1 to bring up the Format Cells dialog.
There, select the Date category and choose the 201-03-14 date format on the right side menu.
Click OK on the Format Cells dialog to transform the date format for the selected cells.
Now, go to the first cell under the Deadline column. Here, you’ll calculate the future date after adding the given days.
There, copy and paste the following formula and hit Enter:
You should get the calculated date in the first cell of the Deadline column. Now, use the fill handle to copy and paste the same formula in all other cells in the same column.
Add Days to a Date Using The WORKDAY Function
The methods explained so far don’t distinguish between weekends and weekdays when adding days to a date in Excel. If you need to calculate a future date after adding only business days, you need to use the WORKDAY function. This method also lets you deduct holidays from the future date calculation.
This technique is specifically beneficial for project planning, employee leave calculation, financial planning, event planning, and more.
Go to your worksheet and create a different column for the list of holidays under the column name Holidays.
Now, go to the first cell below the Deadline column header. There, copy and paste the following formula:
In the above formula,
C4 is the start date,
D4 is the days to be added, and
$G$4:$G$6 is the list of holidays.
Now, hit Enter to get the calculated future date.
You can use the fill handle to apply the same formula to the rest of the cells in the Deadline column in your worksheet.
Add Days to an Excel Date Using Power Query
You can use Power Query to add days to a date in Excel when importing a large dataset from external sources. The method is also applicable to existing datasets of your worksheet.
Firstly, you must import your worksheet. To do this, you can highlight the worksheet dataset and click the From Table/Range command inside the Get & Transform Data block in the Data tab.
Click OK on the Create Table dialog to finally import the dataset to Power Query.
Click on the Add Column tab and choose Custom Column on the General block.
In the New column name field, enter a name for the new column, like Deadline.
Now, inside the Custom column formula field, enter the following formula:
=Date.From(Date.AddDays([Start Date],[ETA in Days]))
The Deadline column will appear in Power Query after adding days to dates.
Right-click on the new Deadline column on Power Query and choose Copy from the context menu.
Close the Power Query window. You can choose to keep or discard the query in the Power Query tool.
Paste the copied dataset next to the ETA in Days column in your worksheet.
A part of your dataset shall show table formatting. To remove table formatting, right-click on the table, hover the cursor on Table, and choose Convert to Range.
If your dataset is on an external server, you can import that to Power Query for calculations by going to the Data tab > Get Data > and choosing any option, like From Database.
In the From Database context menu, choose the final option like From SQL Database, From Oracle Database, etc.
Once you’ve imported a database to Power Query, follow the same steps mentioned earlier to add days to a date in Excel.
Add Days to an Excel Date Using Excel VBA
Finally, you can use Excel VBA to automate the task of adding days to a date in Excel. You don’t need to be a VBA programmer to create a VBA macro that’ll do this job for you. Find below the script you can use:
Dim DateRange As Range
Dim DaysRange As Range
Dim DestinationRange As Range
' Prompt the user to select the date range
On Error Resume Next
Set DateRange = Application.InputBox("Select the range of dates", Type:=8)
On Error GoTo 0
' Prompt the user to select the days range
On Error Resume Next
Set DaysRange = Application.InputBox("Select the range of days to be added", Type:=8)
On Error GoTo 0
' Prompt the user to select the destination range
On Error Resume Next
Set DestinationRange = Application.InputBox("Select the destination range", Type:=8)
On Error GoTo 0
' Check if ranges are set
If Not DateRange Is Nothing And Not DaysRange Is Nothing And Not DestinationRange Is Nothing Then
' Add days to dates and write to destination range
Dim i As Integer
For i = 1 To DateRange.Cells.Count
DestinationRange.Cells(i).Value = DateRange.Cells(i).Value + DaysRange.Cells(i).Value
You might be wondering how to use this VBA script. You can easily create a VBA macro using this code if you read the following article:
📒 Read More: How To Use The VBA Code You Find Online
When you run the macro, first, Excel shall ask you to select the date range in your worksheet.
Then, you’ll see another input box so you can highlight the cell range that contains the days to be added.
Lastly, Excel shall ask you to select the destination cell range for the calculated dates.
Once you interact with these input boxes, Excel will generate the new date values in the selected destination cells.
These are the tried and tested methods to add days to a date in Excel. Try out any or all of the methods mentioned in this article to sharpen your Excel data analysis skills.
Did you like any of these methods? Do you know a better technique to add days to an Excel date? Let our readers know by commenting below.