8 Ways to Add Days to a Date in Microsoft Excel

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.

📒 Read More: 7 Ways to Get the Number of Days in a Month in Microsoft Excel

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.

Create new column
Create new column

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.

Entering the formula
Entering the formula

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:

=C4+D4
Getting the result
Getting the result

Hit Enter to get the output in the selected cell.

Using fill handle
Using fill handle

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.

Example dataset
Example dataset

Suppose, you’ve got a Start Date and ETA in Days column in your dataset. You need to get deadlines for each task.

Renamed column
Renamed column

Change the Start Date column header to Deadline.

Copying dataset
Copying dataset

Now, copy the content of the ETA in Days column by highlighting its data and pressing Ctrl + C on the keyboard.

Bring up Paste Special
Bring up Paste Special

Highlight the Deadline columns data and press Ctrl + Alt + V to open the Paste Special dialog.

Choose Values and Add
Choose Values and Add

On Paste Special, select Values under the Paste section and Add under the Operation section. Click OK to perform the addition action.

Added days to dates in Excel
Added days to dates in Excel

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.

Entering Today formula
Entering Today formula

Now, copy and paste the following formula into that cell and hit Enter:

=TODAY()+19
Added days to today
Added days to today

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:

The 1st Delivery column
The 1st Delivery column

Create an Excel worksheet as shown above and populate the start dates of the deliveries in the 1st Delivery column for each customer.

Drag fill handle
Drag fill handle

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.

The Fill drop down
The Fill drop-down

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.

Series dialog
Series dialog

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.

Added days to dates using Fill Series
Added days to dates using Fill Series

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.

Change date format in Format Cells
Change date format in Format Cells

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.

Entering TEXT formula
Entering TEXT formula

There, copy and paste the following formula and hit Enter:

=TEXT(C4+D4,"YYYY-MM-DD")
Copying TEXT formula using fill handle
Copying TEXT formula using the fill handle

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.

Holidays column
Holidays column

Go to your worksheet and create a different column for the list of holidays under the column name Holidays.

Entering a WORKDAY function
Entering a WORKDAY function

Now, go to the first cell below the Deadline column header. There, copy and paste the following formula:

=WORKDAY(C4,D4,$G$4:$G$6)

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.

Future date using WORKDAY
Future date using WORKDAY

Now, hit Enter to get the calculated future date.

Using fill handle WORKDAY
Using fill handle WORKDAY

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.

From table range
From table range

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.

Create table
Create table

Click OK on the Create Table dialog to finally import the dataset to Power Query.

Add column
Add column

Click on the Add Column tab and choose Custom Column on the General block.

Custom column
Custom column

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]))
Added days to date using Power Query
Added days to date using Power Query

The Deadline column will appear in Power Query after adding days to dates.

Copy deadline
Copy deadline

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.

Pasted deadline column
Pasted deadline column

Paste the copied dataset next to the ETA in Days column in your worksheet.

Convert to range
Convert to range

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.

From database
From database

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.

From SQL database
From SQL 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:

The VBA script
The VBA script
Sub AddDaysToDates()
    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
        Next i
    End If
End Sub

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

Input date range
Input date range

When you run the macro, first, Excel shall ask you to select the date range in your worksheet.

Input box for days
Input box for days

Then, you’ll see another input box so you can highlight the cell range that contains the days to be added.

Destination range
Destination range

Lastly, Excel shall ask you to select the destination cell range for the calculated dates.

Added days to dates using VBA
Added days to dates using VBA

Once you interact with these input boxes, Excel will generate the new date values in the selected destination cells.

Conclusion

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃