If you find the week’s start date, i.e., the beginning of the week, you can determine the end date of the week. To accomplish this, we utilize the WEEKDAY function in Google Sheets.
Why is this formula significant to learn?
Many spreadsheet calculations rely on dates. For instance, when filtering data for recent weeks, you may need to determine the week’s start date from today and then determine the number of weeks you desire from that starting point. You can use them as criteria in the FILTER function.
Formula to find the week’s start date from a given date in cell A1:
=A1-WEEKDAY(A1, 1)+1Formula to find the week’s end date from a given date in cell A1:
=A1-WEEKDAY(A1, 1)+7These formulas are for weeks starting from Sunday. To start from Monday, you must replace type 1 with type 2 in the formula. Refer to the syntax below to understand the position of type in the formula:
WEEKDAY(date, [type])Example: Finding Week Start Dates in Google Sheets
We have a list of dates in the cell range B3:B10. To find the week start date of the date in cell B3, we can use the following formula in cell C3:
=B3-WEEKDAY(B3, 1)+1Click and drag the bottom right fill handle (which turns into a plus sign) in cell C3 down to cell C10.
Alternatively, clear all the values in C3:C10 and use the following array formula in cell C3:
=ArrayFormula(B3:B10-WEEKDAY(B3:B10, 1)+1)
Why Does Formula Show 24/12/1899 as Week Start?
The formula sometimes returns the first day of the week as 24/12/1899 if the type specified in the WEEKDAY function is 1 or 25/12/1899 if the type is 2.
This typically occurs with blank cells in the calculation range. There are several ways to solve this, but I suggest using the following DATEVALUE and IFERROR combination.
Non-Array Formula for Cell B3:
=IFERROR(B3-WEEKDAY(DATEVALUE(B3), 1)+1)Array Formula for Cell Range B3:B10:
=ArrayFormula(IFERROR(B3:B10-WEEKDAY(DATEVALUE(B3:B10), 1)+1))The formula result will be date values, not dates. To format that back to dates, select the result and click Format > Number > Date.
Example: Finding Week End Dates in Google Sheets
Here, things are pretty straightforward. We already have the formula that finds the first day of the week. You just need to add 6 days to it.
The simple way to achieve this is to replace “+1” in the formula with “+7”.
Consider the sample data in the earlier example. In cell D3, enter the following formula and drag it down until cell D10:
=B3-WEEKDAY(B3, 1)+7Alternatively, you can use the following array formula in cell D3 to get the last day of the week:
=ArrayFormula(B3:B10-WEEKDAY(B3:B10, 1)+7)
You only need to consider the DATEVALUE + IFERROR approach if you expect blank cells between dates.
Resources
- Convert Dates to Week Ranges in Google Sheets (Array Formula)
- Calendar Week Formula in Google Sheets to Combine Week Start and End Dates.
- Weekday Name to Weekday Number in Google Sheets.
- How to Find Current Month’s Week Number In Google Sheets.
- Find the Date or Date Range from the Week Number in Google Sheets.
- Reset Week Number in Google Sheets Using Array or Non-Array Formulas.
- How to Populate Sequential Dates Excluding Weekends in Google Sheets.
I want to choose the weekend date from a specific cell on a Spreadsheet and have the week on Friday. Any help with that formula?
For example, cell J2 has a date on it, i.e., Wed, 13 Apr 2022. I want the weekend date column to show Fri, 15 Apr 2022. Please help!
Hi, Jon,
You can try this formula.
=J2-weekday(J2)+6Hey Prashanth
Really helpful stuff and logical when I looked at your formula.
Thanks.
Hi, it is not working, at least for my case, it always returns the day before and not the start day of the week, if I put 07/08/2020, it returns 07/07/2020. I want both dates in one cell, so I’m using this:
=arrayformula(if(G5:G="",,CONCATENATE(TEXT(G5-weeknum(today(),2)+1,"mm/dd/yy")," - ",TEXT(G5-weeknum(today(),2)+1+6,"mm/dd/yy"))))Any help appreciated.
Thanks
Hi, Nick,
The formula is wrong!
To find the week start and the week end date of the date in cell G5 in combined form, use the below formula.
=if(len(G5),text(G5-WEEKDAY(G5,2)+1,"mm/dd/yy")& " - "&text(G5-WEEKDAY(G5,2)+1+6,"mm/dd/yy"),)Best,
Given the start date and calculate the 11th-week date. Can you please tell me the formula?
Assume cell D2 contains the start date. Then to find the 11th-week start date use the following formula.
=D2+(11*7)-WEEKDAY(D2,2)+1My start date is on Sunday. The problem is when the date is on a Sunday, the date will reflect the previous Sunday date instead of putting the date as it is.
Example: I am trying to get the start date of June 9, 2019, which is a Sunday. So, instead of showing June 09, 2019, it will show June 02, 2019 which is the start date of the previous week. How can I fix this?
Hi, Chester B,
The formula is working like this.
given date - weekday(given date)+1Weekday of Sunday is 1.
=date(2019,6,9)-weekday(date(2019,6,9))+1So the above formula is equal to;
=date(2019,6,9)-1+1So I don’t find any issue with my formula.
Would you help me? How can I find the week start date of some week 10 weeks ago (if week starts from Sunday)?
Hi, Shuv,
You can probably use this formula.
=TODAY()-WEEKDAY(TODAY())+1-9*7If you want to find a week start date 5 weeks ago, then replace 9 in the above formula with 4. I mean n-1.
Best,
It works. Really helpful!