22

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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.

How to Add Days to Existing Dates in Excel

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.

Excel Dates Are Stored as Serial Numbers and Formatted as Dates

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.

Formula to Add Days to Existing Date Value in Excel

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.

Paste Special to Add or Subtract Days to Each Date Cell

Here are instructions on how to use the Paste Special Operation to add or subtract dates:

  1. 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.
  2. Copy the cell (Right-click > Copy or Ctrl +C).
  3. Select the cells that contain the dates.
  4. Right-click and choose Paste Special (keyboard shortcut: Alt, E, S).
  5. Select the Values radio button from the Paste section on the Paste Special menu (keyboard shortcut: V).
  6. Select the Add radio button from the Operation section (keyboard shortcut: D).
  7. 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.

Paste Special Operation Modifies Existing Formulas

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.

VBA Macro to Add or Subtract Days to Dates in Excel GIF

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 Sub
Sub Subtract_Day_From_Date()
'Subtracts 1 from the activecell

  ActiveCell.Value = ActiveCell.Value - 1

End Sub

If 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 Sub

The 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.

Assign Macros to Shapes to Call the Add or Subtract Day to Dates VBA Macro

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 22 comments
Mike - January 14, 2018

Hi Jon. Great blog. Is there a way to format a cell to automatically add days as they pass on the calendar (e.g. going forward from a specific date). I’m trying to track the number of days a property is listed on market without having to go in and update (+1) the number manually every day.

Thnx in advance.

Reply
Rivera - January 5, 2018

Happy New Year!

Can you create a part 2 on Date. To show example on obtaining specific date which doesn’t calculate weekends,”Date-how to add dates that doesn’t include weekend (sat/sun)”.

Example:
DOT: 1/5/17, so participant eligible to rollover in 30 day.
Answer: Feb 16th.

Thank you.

Reply
Denise - January 5, 2018

Really enjoyed these videos. Thanks.

Reply
James - January 4, 2018

Good morning Jon.
I am looking to add this type of function into an IF statement.

If Cell A1 contains a date, then add 14 days, if not, leave the cell blank.

I have tried this function
— =IF(E16=”DATEVALUE”,E16+14,” “) – but cannot get it to work. Can you provide any insight? Thanks,

Reply
    Mitch - January 11, 2018

    You could use: =IF(E1>0,E1+14,””) or =IF(ISNUMBER(E1),E1+14,””). These check if the cell contains a number. You could change the 0 to whatever would be the earliest date if there might be other (lower) numbers in those cells.

    Reply
Mohadin - December 29, 2017

You are really nice gentleman

Reply
ABRAHAM LEE - December 25, 2017

Jon, Happy Holidays to you and your family.

Thank you so much for your videos.

Best Regards,

Abraham

Reply
    Jon Acampora - December 27, 2017

    Thank you Abraham! I really appreciate your support. Wishing a happy holiday season to you and your family as well. 🙂

    Reply
Edil - December 23, 2017

Hi Jon,

Thanks again for your valuable explanation on adding dates.
I enjoyed it, particularly the macro part to add days to a multiple-cells range.

Wishing you and family a Happy Holiday season.

K.R.,
Edil Poulina

Reply
    Jon Acampora - December 27, 2017

    Hi Edil,
    Thank you for your support. I’m happy to hear you enjoyed the post. I hope you and your family have a great holiday season and happy new year as well. 🙂

    Reply
prahalad kumar - December 22, 2017

hi

thanks for help us

Reply
Bob Keene - December 22, 2017

Another excellent set of examples Jon.:-) Sure do appreciate you sharing the questions and various solution approaches!!! Happy Holidays…!!!

Reply
S.Yogarajah - December 21, 2017

Macro version is superior to others. Congratulations

Yogarajah

Reply
Carrie - December 21, 2017

Hi Jon, Happy Holidays to you and your family!

Thanks for the Paste Special tip – I only ever use the Paste Value or Paste Format, or Paste Formula. I never really knew how to use the Paste Special operation. I’m not likely to use it, but it’s still cool to know. =P

I have a set of workbooks that I’ve been trying to streamline. To minimize any tying errors, I use as much formulae and calculations as possible & reference them to each other. It’s so messy…

Anyways, this includes dates, b/c I’m just too lazy to have to retype them. The dates I need are every Monday, with exceptions of Monday holidays, and whatever beginning-of-week days that the company decides not to be open for. For ex: next Monday is Christmas, and we’re using a company float day to take Tues off as well. So my data would use the 27th as the first day of that week. I usually type in one date – the very first workday of the first full week of the month – and then use WORKDAY(1stWorkday,5,MondayHolidays) to fill in the following weeks. It kinda works, but I’m always having to manually adjust that number of days though for all the times we end up not being open on a Monday.

I have another workbook where I’m listing the all the days of the month starting from the first full week. I have a generic 12/1/17 etc date in cell A2 formatted to give me a year, and then use CHOOSE(WEEKDAY($A$2,2),$A$2,$A$2+6,$A$2+5,$A$2+4,$A$2+3,$A$2+2,$A$2+1) to get the first Monday of the month. Then all dates after that adds 1 day to the previous row until I reach the end of the last full week of the month.

So yeah, that’s my 2 main ways of adding days to dates. Honestly, it would work just fine if I didn’t need to worry about Monday holidays… Well anyways, hope you have a great holiday!

Reply
    Jon Acampora - December 27, 2017

    Hey Carrie,
    Happy holidays to you as well. Thank you for sharing your tips. Holidays can definitely be tricky when doing date calculations for work days.

    I’ve also found it easiest to keep a list of the holidays or first days of the week, month, quarter, etc. to use with WORKDAY or lookup formulas. Especially if your company is on a fiscal calendar. I’ll add this topic to my list for future posts. I’m sure others would benefit from learning the techniques you are using.

    Thanks again for all your support and have a happy new year! 🙂

    Reply
Larry - December 21, 2017

Good morning Jon,

As always I very much enjoy your videos, very helpful. I always come up with solutions to other challenges while watching your instructional videos.

Best Regards,

Larry

Reply
Ken McMillan - December 21, 2017

Your presentation skills are off the chart! The way you explain things make it so simple to understand the point you are trying to get across. A rare talent you have indeed.

You are to be commended! Best of luck with your blog and Excel courses in the future.

Regards,
Ken McMillan

Reply

Leave a Reply: