Add Days to Date Using an Excel Formula (Easily)
We all have used Excel to add up numbers, subtract them, find their product, divide them, and whatnot.
But have you ever heard of adding and subtracting days from a date in Excel? Yes, you can do that too.
In this tutorial, I will show you multiple methods of how you can do it in Excel. And you’d be amazed to see how simple it gets.
Download your free sample workbook for this tutorial and follow me along the way 🚀
Basic arithmetic
Adding days to a date in Microsoft Excel can be as easy as doing a 2 + 2. Don’t believe me? Check this out 👀
We have a set of dates in Excel in Column A and the number of days to be added to them in Column B.
To add these days to the dates in Column A:
Step 1) Write the following formula in Column C.
Date + Number of Days
Step 2) Drag it down to the whole list.
Tada! All done.
Do your results look like numbers instead of dates?
It’s alright. There’s nothing wrong with these results except for their formatting.
Step 3) Select these numbers and press Control + 1 to launch format cells as below.
Step 4) Select the Date Format from the pane on the left and select the kind of date format you want to be applied (short date, long date, etc.).
And suddenly – everything begins to look fine.
Using basic arithmetic is the most straightforward way to add/subtract days in Excel from some given dates.
Paste Special Feature
I am sure you didn’t know you could use the Paste Special feature of Excel in the way I am going to just show you now 🔦
You can add/subtract days from dates in Excel using it. Here’s how.
Step 1) Select the cells containing the number of days.
Step 2) Copy these by pressing the Control key + C.
Step 3) Select Cell A2 (the first cell of the range where dates are populated).
Step 4) Right-click to launch the drop-down menu > Go to Paste Special.
Step 5) From the Paste Special window, select “Values” and “Add” as the operation.
Step 6) Click okay to see what happens next.
Excel copies the number of days and pastes them on the dates by adding them up.
It is important to use select paste as “Values” or else Excel will also paste the format of days which will then turn the dates into serial numbers.
DATE Function
Days can also be added to dates using the DATE function 📅
Step 1) Begin writing the DATE function.
Syntax of the DATE function has three arguments, the year, the month, and the date.
Step 2) For the year argument, nest in the YEAR function as below.
The YEAR function gives the Year for a supplied date. So, for example, if we supply the date 10 March 2024 to the YEAR function, it will give back 2024.
Step 3) For the month argument, nest in the MONTH function as below.
The MONTH function gives the month for a supplied date. So, for example, if we supply the date 10 March 2024 to the Month function, it will give back 3 (March is the third month).
Step 4) For the day argument, nest in the DAY function as below.
The DAY function gives the number of days for a supplied date. So, for example, if we supply the date 10 March 2024 to the DAY function, it will give back 10 (10th March).
Step 5) To add days to the date, we will add the number of days (Cell B2) to be added to the DAY argument as below.
Hit Enter to get the date with the number of days added.
You might find writing this function a little complicated, but the results are fascinating 😲
WORKDAY.INTL function
Here’s a smart way to add days to a specific date in Excel – by using the WORKDAY.INTL function🤏
The WORKDAY.INTL function of Excel is meant to add a certain number of workdays to a given date and return the resulting workday.
In essence, this function is meant to calculate workdays. However, we can work it around to calculate normal days using it. Let’s do it here.
Step 1) To add 10 days to 15th January 2024 using the WORKDAY.INTL function, write it as below.
The first argument (A2) is the start date to which Excel will add 10 days (B2; the second argument).
The third argument is weekends. This argument is supplied as a string of seven numbers where 0’s represent weekdays and 1’s represent weekends. The purpose of this argument to is give users the discretion to define any combination of weekends.
So, if you specify all the seven numbers as 0’s – technically there are no weekends, and hence there is no difference between workdays and weekends. That’s how we can tweak this function to calculate normal days instead of working days💡
Step 2) Hit Enter to see results.
15th January + 10 days makes 25Th January – straightforward and accurate?
Step 3) Applying the same to the entire list, we get all the required dates.
If the weekend argument (which is an optional argument) is omitted, the WORKDAY.INTL function will assume Saturday and Sunday as weekends. Make sure to include the third argument as “0000000” as the third argument to have this function yield the correct results for us.
Conclusion
These are some easy and interesting methods to add days to dates in Excel. Starting from basic addition to using a simple function to nesting multiple functions together.
This helps with many routine day jobs in Excel, and once you know how it works – you can tweak it further to produce results that meet your needs.
If you enjoyed reading this tutorial, do check out my following Excel tutorials, too: