How to Add Days to Dates in Excel

The Opinion Lighthouse
32 followers
92 Video Views·Nov 8, 2022
How to Add Days to Dates in Excel
Welcome to Excel Campus. My name is John and in this video I'm going to explain the three ways
to add days to a date in Excel. So this was a great question submitted by Michael and he basically
has a column of dates here in his data set and he wants to add a day or a number of days to each of
these dates. So we're going to take a look at a few different ways to go about this technique. So
the first thing we can do is just use a formula to add a day to a date or a number of days to a
date. And in Excel, a date is actually just a number and it's the number of days that have
elapsed since January 1st, 1900. And then it's just formatted as a date. So if we were to change
the number formatting on this date here, we'll just go to the Home tab, the Number Format drop
down, change this to General, we can see we get this number here, 42373. That's just the number
of days that have elapsed since January 1st, 1900. And then Excel just uses this to store the date
value and we actually just apply formatting to view it as a date. So and I have a whole other
video that explains the calendar date system in Excel in a lot more detail and I'll put a link
to that below this video. But what that means is that we can just add numbers to this date. So in
cell B2 here, we can just type a very simple formula, reference cell A2, and then just do a
plus one if we wanted to add one day, and hit Enter. And that will add one day to this date.
And then of course, we could just copy this down, we can just double click the fill handle
to copy this formula down. And then if we wanted to replace our original dates with this new date,
we could do a copy, just right click or Ctrl C copy, and then paste values right here. So we'll
just paste the values right there. And then we can see those new values have been pasted over
the old values. So that's one way to go about it using a formula. And again, that formula could be
any number of days. So we just added a day, we could also subtract a day with a minus one there,
and that would subtract one day from this date. Or if we wanted to add an entire week, we could do
something like plus seven to add a week to that date. So a lot of options there. And it's really
just simple addition or subtraction. So the next technique is using the paste special method. And
this will allow us to actually add days or subtract days from the existing days or dates in
these cells right here. So to use this method, I'm first going to insert the number of days that I
want to add in this case, I'll just put a one here in any blank cell. So in cell C two, I'm going to
enter a one. And then we're going to copy this cell or Ctrl C or right click copy. And then we'll
select the dates here in this column. And then right click paste special right here, right click
paste special. And now we'll open up the paste special menu. And on the paste special menu here,
we have this operation section that will allow us to add, subtract, or even multiply or divide
these selected values by the number that we have copied or by the value that we have copied. So we
currently have one copy to the clipboard. So we can add that number to all of these existing values
here when we do the paste. So we're going to select Add. Now the other thing we want to do here is
choose values from the paste type, because we just want to paste the values. If we don't choose that,
then this will also paste the number formatting of cell C two onto column A here. And that number
formatting is general. So it would basically just mess up our date format. So that's why we'll
choose values there, add operation, and then click OK. And we can see that that's added one day to
each of these dates. So that's a great way to do that. Because you don't have to create a formula
in a separate column and do that copy and paste values, you can just do it very quickly with this
paste special method. And then of course, if you wanted to subtract, you can just right click paste
special keyboard shortcut is Alt-E-S. And then here, instead of add, we could choose values again
and subtract for the operation. And again, this could be any number of days here. So it doesn't
have to be one. It could be seven or whatever number of days you want to add or subtract.
Just put that in there, copy it to the clipboard, and then do this paste special here. And that
would subtract one from each of these dates. Now it's important to note that this is actually
modifying the value in the cell. So as we can see here, there's no formula. If we look here in the
cell or even up here in the formula bar, there's no formula here. We've just added or subtracted
that number from the value in the cell. And the paste special operation does that. If, however,
there was a formula in this cell, in fact, in column B here, we'll just do a quick formula.
We'll do an end of month function here to return the last day of the month for this particular
date. So here we have the end of the month. We'll just use the end of the month function
and copy that down. So now we have basically a formula here that's calculating that date
in this cell. And if we were to do the same thing, so we'll go here, we'll just copy this
cell and then select all of these cells here. And we'll do that same paste special and we'll choose
values and the add and then click okay. What's happened here is that the formula has actually
been modified to have a plus one added to it. So whatever the number is that you've copied,
the formula will actually be modified to have a plus one or a minus one if you do subtract
however many days you have there will be added to the end of that formula. We can also see that
parentheses have been put around that existing formula right here. And then we just have the
plus one at the end. So the paste special operation is smart enough to detect that if you have a
formula, an existing formula there, and then it will just modify that formula. If you don't have
a formula and you just have values in the cells, then it will modify the values in those cells.
Now the third way we can go about this is using a macro. So on this sheet here, I have the same
example. And you'll notice that I have a few little shapes right here. And when we click these,
that will add one to the date, click the left arrow, it will subtract one from the date. And
that is using a macro. So I'll explain how I did this. And you'll be able to download this workbook.
There should be a link below the video where you can download this workbook and check out the code
examples. So we'll just open the VB editor, the developer tab, visual basic button, keyboard
shortcut is Alt F11. And here's the VB editor window. I'll keep Excel open in the background
so we can see both of these. And we're in this project here in this workbook, add date to date.
And in module one are these code examples. So the first macro here, it's very simple. It's just
going to add a day to the date in the active cell. And that's it's just one line of code.
It's this line of code right here. And all it's doing is taking the value in the active cell
and setting that equal to the value in the active cell plus one. So very simple line of code there.
And when we run that macro, we can hit F5 on the keyboard, or the run button right here,
we can see that that's just adding a value, just adding one to this date right here. So
click it again, we can see it's now January 9. And then we also have a macro here to subtract
a day from a date. So it's the same line of code, we just subtract one instead of add one. And
again, this could be any number, it does not need to be one, it could be seven, or any number really.
So that will just work on the active cell, the cell that's selected. If we wanted to apply that
same technique to multiple cells, then I have a macro down here that does that. And in this case,
here, this macro is just going to loop through each cell in the selected cells. So we're using
this variable here. And it's saying for each cell in the selection dot cells, so all the selected
cells, we're again, just going to run that line of code where we take the value of the cell
and set it equal to the value of the cell plus one. Again, you could change that number there.
But in this case here, so if I just select all the cells here in the column, and then I'll jump
back to the VB editor, Alt F11, we'll run this macro here, hit the Run button, we can see now
that all of the cells have one day added to the date. And then I have the same macro down below
that to subtract a day from the date. Again, it's just minus one there instead of a plus one.
And then I also have a macro down here that will only add a day to the dates in a specific
range. So if you wanted to add some buttons to your worksheet, like I have here, in this case,
it's just shapes added to the worksheet. If you want to make sure that the user only runs that
macro on column A here, the column that contains your dates, then we can use this macro here. And
this just has another line of code added to it with an if statement. And what this is doing is
using this intersect function to determine if the selected range, the selection, intersects
range A2 to A20. And of course, you could change this to any range in your workbook. This could be
a table column or a named range. You could change it to any range here, just kept it simple. We'll
keep it as A2 to A20. So if this intersects, if the selected cells intersect that range,
then we're going to run this line of code here, this section of code to loop through each cell
in the selection and add one to it. And this is kind of a weird line of code here. It's saying,
if not is nothing, it's kind of a double negative. But what it really is doing with that double
negative is just saying, if the selection is within this range here, then run this portion
of the code. If not, it'll just jump down to the end if line. So with that, then we can have
multiple cells selected and click these buttons. So we'll jump back over to Excel here.
And if we just, let's say we just select these cells right here and click the plus button,
that's just going to add a day to all of the dates in the selected cells, just like that.
So we can continue to click these buttons. We just have a single cell selected and we'll just
click that button and it'll add or subtract days to the selected cells. So that's a great way to
do it with the macro if you want to make this a little more interactive and easier for your users.
And of course, the possibilities with the macro are just endless there on how you want to set
this up and how you want the user interface to look. That's a very simple example, just using
some shapes. These are just shapes that I created from the insert menu here. Just insert a shape,
which is the triangle shape there, and then right-click the shape and assign the macro to it.
And we can see that that macro there is that add day to range macro. It's added right there. So
pretty simple way to do that with a shape and a macro to make this much more interactive and easy
for your users to add or subtract days from the current date in a cell. And just like everything
with Excel, there are a lot of different ways to solve this problem. So please leave a comment
below with a suggestion you have on how to add a day to a date. If you enjoyed that video, there are
a few simple things you can do to help me out. If you're watching this video on YouTube, click the
like button below the video and leave a comment with any questions or feedback. And please don't
forget to subscribe to my free email newsletter to get more tips and tricks that will help you
learn Excel. Thanks again for watching and I'll see you soon.
