How to Add Days to a Date in Google Sheets

puneet-gogia-excel-champs

- Written by Puneet

Introduction

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

The best method for adding days to a date in Google Sheets is simple addition. Here’s how you can do this:

  1. First, type the equal sign to start the formula.
  2. After that, refer to the cell where you have the date.
  3. Next, enter the plus sign (+) to tell the formula to add.
  4. In the end, enter the number of days to add (1) and hit enter to get the result.
add-days-to-date
=A1+1

In 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. If Sheets doesn’t understand your date, the formula will not work correctly.

Add 7 Days to Today’s Date

You can also use the TODAY function to add specific days to today’s date. TODAY() function is helpful to insert 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.

  1. First, type (=) in a cell to start the formula.
  2. Now, enter the TODAY function to get the today’s date.
  3. After that, type the plus sign (+) to add the days.
  4. In the end, enter the number of days you want to add (7) and hit enter to get the result.
add-seven-days-to-date

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()+7

Important: 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 to 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)
add-days-in-date-in-google-sheets
  • 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

To add a specific number of business days (without weekends and holidays), use the WORKDAY function.

To add 10 business days to 01-Jan-2023:

=WORKDAY(A1,10)
add-specific-business-days
  • 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.

And if you want to also 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.

subtract-days-from-date
=A7-5

In the above example, we subtracted 5 days from 01-Jan-2023, which returned 27-Dec-2022 in the result 5 days before.

SAMPLE SHEET

Last Updated: April 17, 2024