Do you know how to list all Sundays from a start and end date in Google Sheets? If not here is that awesome tips.
You can use the same formula, that I am going to provide you, to populate all the Sundays, Mondays or any other days in a year. What more! You can even populate all the weekends between the provided two dates in Google Sheets.
First, let’s see how to list all Sundays from a start and end date. Then I will explain to you how to tune this formula to list all weekends or any other dates.
How to List All Sundays from a Start and End Date in Google Sheets
Here I am following the steps that I’ve used earlier to populate all the dates between a start and end date.
The logic here is to first populate all the dates between two given dates. Then filter out the dates other than the Sundays.
Note: In this tutorial, I am not going to explain you the first part of the logic. That’s about how to populate dates from a start and end date. For that please follow the below tutorial.
Related: How to Auto Populate Dates Between Two Given Dates in Google Sheets
First, see the formula. The second part, that’s the filtering part, I’ll explain to you after that.
Example Formula 1
For example purpose, I am going to populate all the Sundays in the month of May 2018. If I have the start date in Cell A1 and end date in Cell A2, the formula to list all the Sundays in that month would be as follows.
=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
See the screenshot to understand where to apply the above formula and start and end dates.
If the start date is 01/01/2018 and end date is 31/12/2018, the formula would return all the Sundays during the year 2018.
I’ve already explained to you the logic behind this formula that lists all the Sundays from a start and end date. It’s just populating all the dates and then filtering the Sundays.
Hope you have already followed my above link to understand how to list all the dates for a start and end date.
I’ve extracted the relevant formula from my above example formula 1. Here it’s.
=ArrayFormula(TO_DATE(row(indirect("E"&A1):indirect("E"&A2))))
This formula would return the dates from 01/05/2018 to 30/05/2018. I’ve wrapped this formula with a Query formula to return only the Sundays.
In the example formula 1, the “dayOfWeek” number 1 indicates Sunday. If you want to list all Mondays, then replace the number 1 with 2.
Now you have learned, in Google Sheets how to list all Sundays from a start and end date. What about weekends? It all lies in the Query use. Here is that awesome trick.
How to List All Weekends from a Start and End Date in Google Sheets
I am just modifying the above formula little bit to accommodate 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, the dayOfWeek 1 is Sunday and dayOfWeek 7 is Saturdays. See the output.
That’s all.
Related Google Sheets Formulas:
1. Increment Months Between Two Given Dates in Google Sheets
2. Array Formula to Lookup Date Between Two Dates in Google Sheets
3. How to Format Date, Time, and Number in Google Sheets Query
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!