In Google Sheets, adding days to a date can be done with different functions and formulas. You can use simple additions and functions like DATE and WORKDAY. However, the best method depends on the situation and the type of addition you want.
Adding a Day or Two to a Date in Google Sheets
Simple addition is the best method for adding days to a date in Google Sheets. Here’s how you can do this:
- First, type the equal sign to start the formula.
- After that, refer to the cell where you have the date.
- Next, enter the plus sign (+) to tell the formula to add.
- In the end, enter the number of days to add (1) and hit enter to get the result.
=A1+1In the example above, we have the date 1st January; when you add one day, the formula returns 2nd January in the result.
Note – Make sure the date you are using is valid as per Google Sheets. The formula will not work correctly if sheets don’t understand your date.
Add 7 Days to Today’s Date
You can also use the TODAY function to add specific days to today’s date. The TODAY() function is helpful for inserting the current date automatically in a cell.
This function always displays the current date. Let’s say you want to add 7 days to today’s date.
- First, type (=) in a cell to start the formula.
- Now, enter the TODAY function to get the today’s date.
- After that, type the plus sign (+) to add the days.
- In the end, enter the number of days you want to add (7) and hit enter to get the result.
Today is 22-Nov-2023, and when we add 7 days to it, it returns 29-Nov-2023 in the result, which is 7 days ahead of today’s date.
=TODAY()+7Important: TODAY is a volatile function whose value changes when you change anything in the sheet. But you can change the settings from the File > Setting > Calculation.
Adding Days to a Date in Google Sheets using DATE, YEAR, MONTH, & DAY
These functions can take a date and create a new date with the days added. Before that, let’s have a look at these functions.
- DATE(year, month, day): This function returns a date based on the year, month, and day you specify.
- YEAR(date): Returns the year of a from a date.
- MONTH(date): Returns the month from a date.
- DAY(date): Returns the day from a date.
Let’s say you have a date in cell A1 (e.g., 01-Jan-2023), and you want to add 10 days to this date:
=DATE(YEAR(A1), MONTH(A1), DAY(A1) + 10)
- YEAR(A1): This gets the year from the date in cell A1.
- MONTH(A1): This gets the month from the date in cell A1.
- DAY(A1) + 10: This gets the day from the date and adds 10 days to it.
This formula is handy in situations where you need to add a fixed number of days to a series of dates (such as adding a warranty period to a purchase date, scheduling follow-ups, etc.) and can be easily adapted to add any number of days or even to subtract days (by adding a negative number).
Use WORKDAY for Adding Business Days
Use the WORKDAY function to add a specific number of business days (without weekends and holidays). To add 10 business days to 01-Jan-2023:
=WORKDAY(A1,10)
- A1: This cell contains the start date for the calculation.
- 10: business days you want to add to the date by skipping Saturdays and Sundays.
If you want also to exclude specific holidays, define those dates in a range of cells (example C1:C3) and specify in the formula:
=WORKDAY(A1,10,C1:C3)Note – Remember that Google Sheets counts the start date as day 0 when adding days to dates.
Subtract Days from a Date
And in the same way, you can also subtract days from a date. For this, you need to use the minus instead of plus.
=A7-5In the above example, we subtracted 5 days from 01 Jan 2023, which returned 27 Dec 2022 in the result 5 days before.