Mehrab Imtiaz
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.
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:
DATE+7
The same goes for subtracting the number of days:
DATE-7
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.
Our formula:
=B3+($E$3*7)
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.
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])
Let’s see the function in action as we try to add 7 days to our initial date.
=WORKDAY(B3,$F$3)
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])
The function in action:
=WEEKNUM(B3)
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.
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.
=TEXT(B4,"DDDD")
Bonus: See what happens when you use different day formats:
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.
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.
=CHOOSE(WEEKDAY(B3),"SUN","MON","TUE","WED","THU","FRI","SAT")
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.
