It also makes it really easy when you want to find out the total number of days between two dates, as you can simply subtract one from the other. In this tutorial, I’ll show you a couple of really easy ways to add or subtract days to dates in Excel. So let’s get started!
Add or Subtract Days to Dates in Excel Using Paste Special
Recently, I was working with one of the content writers on one of my projects. I sent her a list of articles with the due dates to the writer (as shown below).
The writer came back to me and asked me to shift all these due dates by 10 days, as she had some urgent stuff to take care of and needed 10 days off. This is a perfect example where I would need to add a fixed number of days to the due date so that I can get the new date for each task. Excel has an in-built Paste Special functionality that allows you to quickly add or subtract a fixed number from a range of selected cells. Below are the steps to add 10 days to the existing dates in our data set: That’s it! The above steps would add 10 to all the dates in the selection.
In case you want to subtract from a date, select the Subtract option in Step 7. This method is best used when you want to add a fixed number of days to dates. In case you want to add a variable number of days to a date, it’s best to use the formula method (covered next).
Add or Subtract Days to Dates Using Formulas
With formulas, you get a lot of flexibility while adding or subtracting dates. For example, you can add a different number of days to date by just having that number in an additional column. You can also only add the weekdays (while ignoring the weekends). Let’s see a couple of examples that will make these scenarios easy to understand.
Adding Fixed Number of Days to a Date
Suppose you have a data set as shown below and you want to add a fixed number of days to each of these dates.
Below is the formula to do this:
Since dates are stored as numbers in Excel, all you need to do is add the value by which you want to extend the date. In this example, I have simply added 10 to the date in each cell to get the date after 10 days. You can copy and paste the above formula to all the cells in the column to apply it for all the dates.
Adding Varying Number of Days to a Date
What if you don’t want to add the same number to each date? What if you want to have a variable date added two different existing dates. For example, in some of these dates, I may want to add 10 days, and in some others, I may want to add 5 days or 15 days. In such a scenario, it’s best to insert a helper column and have your variable dates in that helper column. Below I have the example data set where I have a helper column (columns C) it has the days to add.
Below is the formula that I can use to add these days in the helper column to the existing dates:
This is pretty straight forward, as we are simply adding two numbers. And in case you want to subtract days from a date, use the minus sign instead of the plus sign in the above formula. But now, what if only want to add weekdays to the existing date (i.e., ignore the weekends – Saturday and Sunday)? You can do that as well (as covered in the next section).
Adding Only Weekdays to a Date
Below I have the data set where the dates are in column B and the number of weekdays that I want to add are in column C.
Below is the formula that would give you the date after adding the given number of working days:
WORKDAY function in Excel takes three arguments:
The date to which you want to add a specific number of workdays (B2 in our example) The total number of workdays that you want to add (C2 in our example) [Optional Argument] List of holidays that should not be counted
In our example, since we do not have the list of holidays, I am only using the first two arguments. This function only counts the weekdays (i.e., Monday to Friday), and ignore the weekends days (i.e., Saturday and Sunday). In case you want to add weekend days that are different than Saturday and Sunday (say Friday and Saturday or only Sunday), you can do that by using a slightly different formula. Below is the formula that would give me the final result considering Friday and Saturday as the weekend days The WORKDAY.INTL formula is a slight improvement over the WORKDAY function, as it allows you to choose the weekend days.
For example, in the above formula, I have specified the third argument as 7, which indicates that I want to consider Friday and Saturday as the weekend days. Similarly, you can also choose to only have Sunday as the weekend day. In fact, you can choose to have any single day or combination of two consecutive days as the weekend days within this formula. These are two simple ways you can use to add or subtract days from a date in Excel. If you want to quickly add a fixed number of days, you can use the paste special technique or a simple addition formula. In case you want to only add the weekdays and ignore the weekends, you can also do that using the WORKDAY or the WORKDAY.INTL formulas. I hope you found this tutorial useful. Other Excel tutorials you may like:
How to SUM values between two dates (using SUMIFS formula) Calculate the Number of Months Between Two Dates in Excel How to Stop Excel from Changing Numbers to Dates Automatically How to Calculate the Number of Days Between Two Dates in Excel How to Calculate Age in Excel using Formulas + FREE Calculator Template How to Add Months to Date in Excel How To Subtract In Excel (Subtract Cells, Column, Dates/Time) Get Day Name from Date in Excel How to Autofill Only Weekday Dates in Excel (Formula)
