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

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

HELLO SIR, COULD YOU HELP ME PLEASE? I WANT THE DATE CHANGE EVERY DAY FOR EXAMPLE: I PUT 01-JAN-2020 THEN NEXT DAY WHEN I OPEN THE EXCEL FILE THE DATE MUST BE CHANGE TO 02-JAN-2020 OR AFTER 5 DAYS I OPEN IT THE DATE MUST BE CHANGE TO 06-JAN-2020

THANK YOU SIR.

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?

It was very useful article, thanks a lot for the guidance

Great job sir

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.

Very.useful.tips…Keep.it.up.Jon…I.enjoy.your.classes…Thank.you

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.

Really enjoyed these videos. Thanks.

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

You are really nice gentleman

Jon, Happy Holidays to you and your family.

Thank you so much for your videos.

Best Regards,

Abraham

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

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

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

hi

thanks for help us

Thanks Prahalad! 🙂

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

Thanks for your support, Bob! And happy holidays to you as well. 🙂

Macro version is superior to others. Congratulations

Yogarajah

Thanks Yogarajah! I like macros too 😉

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

Thank you Larry! I’m happy to hear you are enjoying the videos and learning something new. 🙂

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

Thank you so much for the nice feedback Ken! I really appreciate your support. You made my day! 🙂