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.

Dates and days in Excel

To add these days to the dates in Column A:

Step 1) Write the following formula in Column C.

Date + Number of Days

Click to copy
Adding days to new date

Step 2) Drag it down to the whole list.

Date formula dialog

Pro Tip!

Did you note some negative numbers as days in between? In Cell B4, B6 and B8.

When a negative value is summed up with a date it is just like deducting a value from a number in Excel. Excel subtracts that number from the given date 🤨

For example, for the date in Cell A2, Excel subtracted 33 days from 20-Feb-2024 resulting in 18 Jan 2024. Subtracting a date in Excel is as simple as adding up – it’s just like rewinding in the past.

Tada! All done.

Do your results look like numbers instead of dates?

Serial 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.).

Date format from number format

And suddenly – everything begins to look fine.

Excel Date format restored

Using basic arithmetic is the most straightforward way to add/subtract days in Excel from some given dates.

Pro Tip!

You can also add any number of days to the current date by using the TODAY function.

Click to copy

TODAY function returns the present date and with the basic arithmetic formula above, you can add days to the current date.

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.

Cell A1 with 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.

Drop-down menu

Step 5) From the Paste Special window, select “Values” and “Add” as the operation.

Values and Add 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.

end date calculated

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.

Kasper Langmann, co-founder of Spreadsheeto

DATE Function

Days can also be added to dates using the DATE function 📅

Step 1) Begin writing the DATE function.

Click to copy
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.

Click to copy
YEAR function nested

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.

Click to copy
ctrl MONTH function nested

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.

Click to copy
DAY function nested

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.

Click to copy
DAY + Number of days

Hit Enter to get the date with the number of days added.

Date value plus days added

You might find writing this function a little complicated, but the results are fascinating 😲

due date for the whole list

Pro Tip!

With the DATE function, you can also add or subtract months and years to/from a given date. For example, if we want to add 2 (or any number of months) to a given date, the above function will become:

Click to copy

Similarly, you can add 2 years to a given date by amending the above function as:

Click to copy

This gives you the freedom to calculate dates with any number of days, months, or years added.

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.

Pro Tip!

The WORKDAY.INTL function is an upgraded version of the WORKDAY function.

The main difference between both is that the WORKDAY.INTL function allows you to specify any day of the week as weekend. The WORKDAY function assumes Saturday + Sunday as weekends with no option to specify anything different.

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.

Click to copy
The WORKDAY.INTL custom format

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.

The WORKDAY.INTL calculates normal days

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.

specified number days added to all 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.

Kasper Langmann, co-founder of Spreadsheeto

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: