Do you know how to list all Sundays from a start and end date in Google Sheets? If not, here’s an awesome tip for you!
You can use the same formula I’ll provide to populate all Sundays, Mondays, or any other day of the week within a given date range. What’s more, you can even populate all weekends (Saturdays and Sundays) between two dates in Google Sheets.
First, let’s see how to list all Sundays between a start and end date. Then, I’ll explain how to adjust this formula to list weekends or any other days of the week.
How to List All Sundays from a Start and End Date in Google Sheets
In this tutorial, I’ll be following the steps I’ve used previously to populate all dates between a start and end date.
The logic here is simple: first, populate all the dates between the given start and end date. Then, filter out the dates that aren’t Sundays.
Note: In this tutorial, I will not explain the first part of the logic, which is how to populate all the dates between two given dates. For that, please refer to the following tutorial:
Related: How to Auto Populate Dates Between Two Given Dates in Google Sheets
First, take a look at the formula. I’ll explain the filtering part (to show only Sundays) afterward.
Example Formula 1
For this example, let’s populate all the Sundays in the month of May 2018. If the start date is in cell A1 and the end date is in cell A2, the formula to list all the Sundays between these dates would be:
=QUERY(
ARRAYFORMULA(TO_DATE(ROW(INDIRECT("E"&A1):INDIRECT("E"&A2)))),
"SELECT Col1 WHERE dayOfWeek(Col1)=1"
)Functions Used: QUERY, ARRAYFORMULA, TO_DATE, ROW, INDIRECT
How it works:
- This formula generates all the dates between the two specified dates in A1 and A2.
- Then, the QUERY function filters the result, selecting only the rows where the day of the week is Sunday (
dayOfWeek(Col1)=1).
Example Output
For a start date of 01/01/2018 and an end date of 31/12/2018, the formula will return all the Sundays of the year 2018.
I have already explained the logic behind this formula in my earlier example. It first generates all the dates and then filters out everything except Sundays.
If you’re unfamiliar with how to populate all the dates between a start and end date, please refer to the previous link.
Here’s the relevant formula from Example Formula 1:
=ARRAYFORMULA(TO_DATE(ROW(INDIRECT("E"&A1):INDIRECT("E"&A2))))This formula generates the dates from 01/05/2018 to 31/05/2018. When wrapped with the QUERY function, it filters out everything except Sundays.
In the formula, dayOfWeek(Col1)=1 specifies Sunday. If you want to list all Mondays, replace the 1 with 2.
Now that you know how to list all Sundays in Google Sheets, let’s move on to listing weekends.
How to List All Weekends from a Start and End Date in Google Sheets
To list both Saturdays and Sundays (weekends), you can modify the formula slightly to include Saturdays.
Example Formula 2
=QUERY(
ARRAYFORMULA(TO_DATE(ROW(INDIRECT("E"&A1):INDIRECT("E"&A2)))),
"SELECT Col1 WHERE dayOfWeek(Col1)=1 OR dayOfWeek(Col1)=7"
)In this formula:
dayOfWeek(Col1)=1refers to Sunday.dayOfWeek(Col1)=7refers to Saturday.
This formula will return all Saturdays and Sundays between the given start and end date.
Final Thoughts
That’s all for listing Sundays or weekends in Google Sheets! You can easily modify these formulas to include other days of the week by changing the dayOfWeek values.
Related Google Sheets Formulas:
- Increment Months Between Two Given Dates in Google Sheets
- Array Formula to Lookup Dates Between Two Dates in Google Sheets
- How to Format Date, Time, and Number in Google Sheets Query
- Array Formula to Generate Bimonthly Dates in Google Sheets
- How to Generate Next Available Date in Google Sheets
- Populate an Entire Month’s Dates Based on a Drop-down in Google Sheets
- How to Populate Sequential Dates Excluding Weekends in Google Sheets
- Find Missing Sequential Dates in a List in Google Sheets
- LIST_ALL_DATES Named Function in Google Sheets (Array Formula)
A very helpful tutorial. Thanks.
Any idea how I can populate two cells with the same date while carrying on the sequence?
For example, A1 and A2 be 9/4/22, then A3 and A4 be 9/11/22, and so on.
Hi, Cody Donton,
It seems you want to list all Sundays twice in a sequence.
If that is the case, you can use this formula in A1.
=filter(date(2022,9,4)+int(sequence(100*2,1,0)/2),weekday(date(2022,9,4)+int(sequence(100*2,1,0)/2))=1)
The formula will return the dates 4-Sep-2022, 4-Sep-2022, 11-Sep-2022, 11-Sep-2022, 18-Sep-2022, 18-Sep-2022, and so on.
To get more dates in the sequence, replace 100, which appears twice in the formula, with a large number.
Hi there – here’s a solution that doesn’t need
=query()to generate a list of weekdays (in this case Mondays) between your start and end dates:=sequence(round(days(A2,A1)/7)+1,1,A1,7)Hi, Stephen Wheeler,
Thanks for sharing.
It seems you require to change A1 in the last part of the formula with
(A1-weekday(A1))+9to return Monday irrespective of the weekday in A1.E.g.:
=sequence(round(days(A2,A1)/7)+1,1,(A1-weekday(A1))+9,7)So helpful, thank you! How could I modify this to have two dates populate in the same cell, so a week (Sunday-Saturday) is listed?
Hi, Rose Pascoe,
This may help.
Calendar Week Formula in Google Sheets to Combine Week Start and End Dates.
Dear InfoInspired,
This was a very helpful tutorial. Is there any way I can populate all dates in a single row rather than a column? Please let me know, it will be very helpful.
Thank you
Ekta
Hi, Ekta Makwana,
Use TRANSPOSE to change the orientation. Here you go!
=transpose(query(ArrayFormula(TO_DATE(row(indirect("E"&A1):indirect("E"&A2)))),"Select Col1 where dayOfWeek(Col1)=1"))Thank you Prashanth, you halped me too!