Google Sheets Date 7 Days: Add a Week to Dates
Master how to handle google sheets date 7 days by adding or subtracting seven days, creating rolling 7-day windows, and troubleshooting date formatting with practical examples for students, professionals, and small business owners.
Within Google Sheets, you’ll learn how to add or subtract seven days from any date and generate rolling 7-day windows for schedules. The key requirements are a valid date cell, consistent formatting, and awareness of locale settings. By the end, you’ll apply simple formulas like +7, -7, and DATEVALUE to real tasks.
Understanding date math in Google Sheets
Dates in Google Sheets are stored as serial numbers that can be manipulated with arithmetic. A date like 2026-03-12 is stored as a numeric value, and adding or subtracting a simple number adjusts the date by that many days. This makes it straightforward to implement a 7-day calculation across a calendar or schedule. According to How To Sheets, recognizing that dates are numbers first helps prevent formatting surprises when you copy formulas across cells or columns. You’ll gain intuition for when to wrap results with DATE or DATEVALUE to maintain consistency across locales and date formats.
Ways to add exactly 7 days to a date
The simplest method is direct arithmetic. If A2 contains a date, enter =A2+7 to get the date a week later. If you’re working with text dates, convert them with DATEVALUE, then add or subtract days. For example, =DATEVALUE("2026-03-12")+7 yields a new date value, which you should format as a date using the Format menu. To stay robust across sheets, consider using the DATE function with explicit year, month, and day to avoid locale-related misinterpretations.
Subtracting 7 days and calculating rolling windows
Subtracting 7 days is just as easy: =A2-7. This is useful for creating rolling deadlines or past-week comparisons. If you’re building a 7-day window, you can place start dates in column A and use a companion column to show the end date with =A2+6 (inclusive window). For business calendars, you may prefer NETWORKDAYS or WORKDAY to skip weekends; pair these with -7 when appropriate to model working days only. In many scenarios, a simple +7/-7 approach is enough, but knowing when to expand to a business-days-only calculation saves time.
Using WEEKDAY, NETWORKDAYS, and date boundaries
WEEKDAY helps you identify the day of the week for any date, which is useful when your workflow depends on weekends or specific weekdays. Example: =WEEKDAY(A2,2) returns 1–7 with Monday as 1. NETWORKDAYS provides a working-days count between two dates, excluding weekends and optionally holidays. For a 7-day window that excludes weekends, you can calculate based on TODAY() and TODAY()+7, then adjust with NETWORKDAYS to count only weekdays. These tools enable more nuanced date logic beyond simple +7/-7 arithmetic.
Working with 7-day windows across a range
When you’re applying a 7-day window to a range, an ArrayFormula is your ally. For instance, =ArrayFormula(A2:A + 7) spills a 7-day offset across the entire date column. To constrain the window to a specific range, use FILTER with conditional logic, such as =FILTER(B2:B, A2:A>=startDate, A2:A<=endDate). Remember to format results as dates. If your sheet contains blank rows, wrap the array formula with IFERROR to keep the sheet tidy.
Handling date formats and locale issues
Date interpretation varies by locale. A 12/03/2026 entry might be December 3 or March 12, depending on settings. To reduce confusion, set your spreadsheet locale under File > Spreadsheet Settings, and use DATE(year, month, day) to construct dates explicitly. When importing data, use DATEVALUE with known date formats and avoid mixing text dates with numeric dates. If you see a #VALUE! error after adding days, check that the cell truly contains a date and not a text string.
Practical examples for common tasks
- Schedule reminders: If today is a date in A2, =TODAY()+7 will show the reminder date a week ahead. Format as date to keep it readable. This simple approach is reliable for weekly reminders and recurring tasks.
- Create a 7-day forecast: In B2, place =A2+7, and fill down to project a week-long timeline from your start dates in A. Use conditional formatting to highlight upcoming dates or overdue items.
- Compare weeks: In C2, use =A2+7 and in D2, =A2+14 to compare current week, next week, and two weeks ahead. This helps with planning and capacity analysis over time.
Common pitfalls and troubleshooting
Dates copied from other systems can become text. If a date is treated as text, DATEVALUE can convert it, e.g., =DATEVALUE(A2)+7. If you see inconsistent results across sheets, verify the regional settings and the date format of the source data. When using TODAY() or NOW(), remember these are volatile functions that recalculate, so your 7-day window may shift on every open.
Templates and automation ideas
Create a reusable 7-day window template: one column for start dates, one for end dates, and another for the 7-day forecast. Save as a template with predefined formats and conditional formatting rules to instantly create date ranges across multiple projects. You can also build a small automation using Apps Script to alert you when dates fall within a critical window, ensuring deadlines aren’t missed.
Tools & Materials
- Google Sheets access(Ensure you can edit a shared sheet or create a new one for practice.)
- Sample date data(Date values in a single column to practice +7/-7 operations.)
- Date formatting presets(Set up a date format like YYYY-MM-DD for consistency.)
- Locale check(Verify sheet locale under File > Spreadsheet settings.)
- Optional: Apps Script (for automation)(Basic alert on date window (optional).)
Steps
Estimated time: Total: 25-40 minutes
- 1
Open a new or existing sheet
Launch Google Sheets and open the document where you’ll work with date calculations. Ensure you have edit access and a clear column for your dates.
Tip: Use a dedicated sheet tab to keep date calculations isolated. - 2
Enter or confirm a date
In a cell (e.g., A2), enter a valid date or reference an existing date. If your data comes as text, convert it with DATEVALUE before applying seven-day logic.
Tip: Make sure the cell is formatted as Date; otherwise arithmetic may fail. - 3
Add seven days with simple arithmetic
In B2, type =A2+7 to calculate the date one week later. Copy the formula down to extend to a range.
Tip: If you copy across columns, ensure the reference remains correct (use absolute references if needed). - 4
Subtract seven days for past windows
In C2, type =A2-7 to see the date one week earlier. This supports historical comparisons and reporting.
Tip: Use formatting to distinguish past vs future dates visually. - 5
Use DATEVALUE for text dates
If dates are stored as text, convert with =DATEVALUE(A2) before adding days.
Tip: DATEVALUE returns a serial date number; you can then apply +7 or -7. - 6
Create a rolling 7-day window
In D2, set =A2+7 and in E2 =A2+13 to show a 7-day window across a range. Extend formulas with ArrayFormula for large datasets.
Tip: For large data sets, use ArrayFormula to auto-fill without dragging. - 7
Work with weekdays only
If weekends should be skipped, combine with NETWORKDAYS or WORKDAY to create business-day windows.
Tip: Include holidays by listing them in a range and referencing that range in NETWORKDAYS. - 8
Validate locales and formats
Check File > Spreadsheet settings to align locale and date formats with your region to avoid misinterpretation.
Tip: Consistent locale reduces misread dates when sharing sheets.
FAQ
How do I add seven days to a date in Google Sheets?
Use simple arithmetic like =A2+7 to get the date a week later. If the date is text, first convert it with DATEVALUE, then add 7. Always format the result as a date for readability.
In Google Sheets, simply add seven days with =A2+7, or convert text dates with DATEVALUE before adding days.
What if the date is in text format?
Convert with DATEVALUE to a serial date, then apply +7. If you have mixed data, apply DATEVALUE only to the text dates and maintain numeric dates as-is.
If your date is text, convert it with DATEVALUE before adding days.
How can I subtract seven days from today?
Use =TODAY()-7 to get the date exactly one week before today. You can combine with other date functions for dynamic dashboards.
Use =TODAY()-7 to back up seven days from today.
Which function helps create a 7-day business window?
Combine =TODAY() or a date with NETWORKDAYS or WORKDAY to skip weekends and holidays when building a 7-day window.
Use NETWORKDAYS or WORKDAY to account for weekends and holidays in a 7-day window.
Why does my date change when I copy it to another cell?
Check that the formula references are correct and that the destination column is formatted as Date. Relative references can shift when copied across columns.
Make sure the destination cell is formatted as date and the references aren’t shifting unexpectedly.
Watch Video
The Essentials
- Add or subtract 7 days with simple arithmetic
- Use DATEVALUE for text dates to enable arithmetic
- Leverage WEEKDAY/NETWORKDAYS for nuanced date logic
- Set locale to prevent date-format confusion
- Create reusable templates for 7-day windows
