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

31 comments

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

  • I know I’m a little late to the party here, but Im trying to pull a date from a previous sheet and add it to the next sheet, while adding 7 days to the date. Is there a macro on how to do this? I’ve already found a Macro to pull information from a previous sheet, but I’m trying to use it to add days.. And I can’t. The cell I’m using is K1 and I’ve tried every combination I can think of with everything that’s listed here.. The macro I’m using is:

    Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
    PrevSheet = Worksheets(xIndex – 1).Range(RCell.Address)
    End Function

    What am I doing wrong? What would the formula or macro be that would give me the result I’m looking for?

  • Hi Jon

    I have a dataset depicting a dataset for a Service desk. All calls logged with the relevant dates are shown.

    What formula can I use to show eg. 1. the number of calls older than 3 days?

    I enjoy your videos and your content are relevant and insightful.

  • I’m currently trying to calculate the days a job post has been open. I’ve used the: =DATEDIF(U2,V2,”D”), and it works perfectly to find out how many days it took to fill a position. BUT what I need in the next column is how long a job post has been opened for in days, but stop calculating if the job has been filled,hold, or cancelled.

  • Hi Jon,

    I’m almost a complete neophyte with excel and I’m trying to figure out a macro to automatically advance the start and end dates in a date range slicer.

    I recorded a macro of me manually adjusting the range and it looks like this:

    Sub Date_and_data_refresh()

    ‘ Date_and_data_refresh Macro
    ‘ Automatically modifies date range and refreshes data


    ActiveWorkbook.SlicerCaches(“NativeTimeline_CreatedDateTime”).TimelineState. _
    SetFilterDateRange “1/24/2018”, “1/31/2018”
    ActiveWorkbook.SlicerCaches(“NativeTimeline_CreatedDateTime”).TimelineState. _
    SetFilterDateRange “1/25/2018”, “1/31/2018”
    ActiveWorkbook.RefreshAll
    End Sub

    What i’m trying to figure out is how to get the SetFilterDateRange to not be date specific but instead to just advance the slicer ranges start date by one day and the end date by one day where the start date would be 7 days prior to today’s date and the end date would be yesterday’s date so that I can just run the macro daily and always have it update the slicer to the last 7 days.

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

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

  • 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,

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

    • Hi There, I believe you looking for this :

      =IF(ISBLANK(E16),”XX/XX/2018″,E16+14)

      This works really nice.
      If you want to replace “XX/XX/2018” by simply: “” it will show blank as you asked.
      Was just showing how easy you can change it to put whatever you want

      Good Luck

  • 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

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

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

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

      • I have a set date, in the next column I need to return a 21 day due date, so I just use =A2+21, however, I need to work this out to 21 workdays. How can I exclude weekends and holidays?

  • 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

  • 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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly