Add 7 Days to Date in Google Sheets (And More)

Date calculations in a spreadsheet are more common than you think. One of the more usual calculations is the addition and subtraction of days and months. But for today, we will focus on how to add 7 days to a date in Google Sheets on top of other similar but important week-related date calculations.

Let’s get started.

Add 7 Days to Date in Google Sheets

All dates in Google Sheets are represented by an integer. This integer is essentially the ‘day number’ of that particular date. We can find this value using the DATEVALUE function.

finding the value of a date using datevalue function

This means that each date integer is unique and performing calculations in terms of days is now that much easier.

So, when we say we want to add 7 days to a date, we simply use addition:


add 7 days to a date in google sheets

The same goes for subtracting the number of days:


subtract 7 days from a date in google sheets

Automatically Add or Subtract 7 Days from Date in Google Sheets

With the fundamentals out of the way, we can look at some advanced techniques we can use for more official tasks. The most common would be to automate the calculation.

Keeping in mind that 7 days is a week, we can define the number of weeks to add in a separate cell.

dataset to ad or subtract 7 days from date in google sheets

Our formula:


add 7 days to date as weeks in google sheets

Note: We have used absolutes ($) around E3 to lock that reference in place as we use the fill handle down the Date After column.

This formula will automatically add the number of weeks to all the dates. It is dynamic and will update as you change the number of weeks.

add 7 days to date as weeks in google sheets animated

If you want to see the date before a certain number of weeks, simply subtract instead of adding in the formula.

Other Important Week Calculations in Google Sheets

1. Add or Subtract Business Days from a Date

In many professional settings, days are counted in the form of only business days, which is usually Monday to Friday. This leaves Saturdays and Sundays out of the equation.

While this is a common calculation, the calculation itself can be tricky if we are going by the usual arithmetic methods of addition and subtraction.

For that, we have a special function called WORKDAY.

WORKDAY(start_date, num_days, [holidays])

workday function syntax

Let’s see the function in action as we try to add 7 days to our initial date.


using workday function to add 7 business days to date in google sheets

You can already see the difference in results. Adding 7 days regularly simply adds the number of days, whereas adding business days skips over the weekends to give a different result.

You can clearly see the difference of the skipped 2 day weekend in our first result.

2. How do I Find the Week Number from a Date?

Finding the week number of a date may be crucial for many reports. Thankfully, Google Sheets allows us to extract the week number from a date without having to resort to complex mathematical formulas. We are talking about of course the WEEKNUM function.

WEEKNUM(date, [type])

weeknum function syntax

The function in action:


finding the number of the week from a date using the weeknum function

As you may have noticed, the week number count starts from the beginning of the year the date is in. Thus, we have a higher number for the year 2021 and lower for the year 2022.

The WEEKNUM function can also be used to find the number of weeks between two dates in Google Sheets. But the dates have to be in the same year.

finding the number of weeks between two dates in google sheets

3. Find Weekday Name from a Date

There are many ways to format a date in Google Sheets. One way is to convert a numerical value to its respective name. We will take advantage of this formatting procedure to find the weekday name of a given date.

Since we are converting a date (number) to text, we must take the help of the TEXT function. The rest is about knowing the format code for ‘Day’ in Google Sheets, which is “DDDD” for the full name.


finding the weekday name in google sheets using the text function

Bonus: See what happens when you use different day formats:

different day formats for date in google sheets

Alternatively, we can take advantage of the WEEKDAY function to customize how our day names appear in cells.

On its own, the WEEKDAY function returns the day number of the week from a given date. Starting from Sunday, which has a value of 1; Monday, which has a value of 2, and so on.

finding the day index of a week day using the weekday function

To actually return a day name, we have to combine a certain function with WEEKDAY. That is the CHOOSE function, which will output our day name.


presenting custom weekday names using choose and weekday functions

Final Words

Adding 7 days to a date in Google Sheets is a simple affair as the application defines each date with a unique integer for the day number. This makes any day calculation easy.

But Google Sheets is not limited to just addition, it has many ways to utilize weeks in a multitude of calculations.

Feel free to leave any queries or advice you may have for us in the comments section below, or have a look at other date-related articles we have that are sure to interest you.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply