3 Ways to Add or Subtract Days to a Date
Bottom line: Learn 3 different ways to add or subtract days to dates in Excel using formulas, copy & paste, and VBA macros. Includes video tutorial.
Skill level: Beginner
Download the File
Download the Excel file used in the video.
Add Day To Date.xlsm (25.0 KB)
How to Add Days to a Date?
Michael asked a great question on how to add days to existing cells that contain dates. He has a data set with a column of dates, and wants to be able to easily add or subtract days to the dates.
This is useful when your system has the wrong date or time setting, or if you are trying to create forecast data for a certain number of days in the future.
So let's take a look at three methods to shift dates by a specific number of days.
Method #1: Use a Formula
The easiest option is to use a simple formula to add or subtract the number of days to the cell that contains the date.
In Excel, dates are actually numbers that are formatted as a date. If you change the number formatting of a cell that contains a date to General or Number, you will see number between 1 and 40,000+. This is the number of days that have elapsed since January 1, 1900, the day the calendar starts in Excel.
Checkout my article & video on how the date system works in Excel for more on this.
One day in Excel is represented by one whole number. Therefore, we can easily offset the date by adding or subtracting a whole number (the number of days).
In the example below we create a simple formula that references the cell that contains the date (A2) and add 1 to it. Copy the formula down to create a column of the new date that has one day added to it.
You can then copy and paste values over the existing date column to complete the update. This technique will work great if your data is the source of a pivot table and you want your reports to use the existing date field.
Checkout my free webinar on pivot tables to learn more about getting your source data ready.
Method #2: Paste Special Operation
The Paste Special feature is another option when you don't want to create a separate column of formulas.
Paste Special has Operations that allow us to add, subtract, multiply, or divide existing values by a number.
Here are instructions on how to use the Paste Special Operation to add or subtract dates:
- Type the number of days you want to add or subtract in a blank cell. For this example I entered a 1 in cell C2.
- Copy the cell (Right-click > Copy or Ctrl +C).
- Select the cells that contain the dates.
- Right-click and choose Paste Special (keyboard shortcut: Alt, E, S).
- Select the Values radio button from the Paste section on the Paste Special menu (keyboard shortcut: V).
- Select the Add radio button from the Operation section (keyboard shortcut: D).
- Click OK or press Enter.
The copied number (1) will be added to the values in each cell.
The full keyboard shortcut to Paste Special > Add > Values is:
Alt, E, S, V, D, Enter (press & release each key in order)
How does Paste Special Work?
The Paste Special Operation will modify the value in the cell if the cell contains a values.
If the cell contains a formula then the formula will be modified and a +1 will be added to the end of each formula. I explain this in more detail in the video above.
The Subtract operation can be used to subtract days from the existing dates. If you want to add or subtract a different number of days, then put a different number in the blank cell in step 1.
This is a quick way to add or subtract days without having to create a new column of formulas.
Method #3: VBA Macro
If this is a task you do frequently, or want the users of your spreadsheet to be able to add and subtract dates, then a macro is a great solution. A macro can be used to change the existing value in the cell(s) by any number of days you specify in the code.
Here are some simple VBA macro code examples that add or subtract days to the active (selected) cell.
Sub Add_Day_To_Date()
'Adds 1 to the active cell
ActiveCell.Value = ActiveCell.Value + 1
End SubSub Subtract_Day_From_Date()
'Subtracts 1 from the activecell
ActiveCell.Value = ActiveCell.Value - 1
End SubIf you want to modify the dates in multiple cells then we can use a For Next Loop to loop through the range and modify each cell.
Sub Add_Day_To_Range()
'Adds 1 to each cell in the selected range
'For Next Loop explained: https://www.excelcampus.com/vba/for-each-next-loop/
Dim c As Range
For Each c In Selection.Cells
c.Value = c.Value + 1
Next c
End SubThe example file (download above) also contains a sheet with buttons in the header cell. These buttons are triangle shapes that are assigned to the Add and Subtract macros.
This makes it easy for your users to modify dates in the selected cell without having to type anything. There are a lot of possibilities with a macro based solution.
Checkout my free webinar on The 7 Steps to Getting Started with VBA to learn more about macros.
Other Techniques for Adding Days to Dates?
There are a lot of ways to accomplish this task in Excel. Please leave a comment below and share a technique that you use to add days to dates. I'm looking forward to learning something new.
Thank you! 🙂