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)=1 refers to Sunday.
• dayOfWeek(Col1)=7 refers 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)