Do you want to effortlessly list all Sundays or even all weekends between a start and end date in Google Sheets? Look no further! In this article, I’ll share with you an awesome formula that will allow you to populate these specific dates effortlessly.
How to List All Sundays from a Start and End Date in Google Sheets
To list all the Sundays between a start and end date, we’ll follow a simple two-step logic.
-
First, we’ll populate all the dates between the given start and end date using a previously explained method. If you’re not familiar with this process, you can refer to this helpful tutorial on How to Auto Populate Dates Between Two Given Dates in Google Sheets.
-
Next, we’ll filter out only the dates that fall on Sundays.
Here’s an example formula you can use:
=query(ArrayFormula(TO_DATE(row(indirect("E"&A1):indirect("E"&A2)))),"Select Col1 where dayOfWeek(Col1)=1")In this formula, you’ll replace A1 and A2 with the appropriate cells that contain your start and end dates. The resulting output will display all the Sundays between those dates.
You can also modify this formula to list other specific weekdays. For instance, if you want to list all Mondays, simply replace the number 1 in the formula with 2.
Now that you know how to list all Sundays, let’s dive into listing all weekends!
How to List All Weekends from a Start and End Date in Google Sheets
To include Saturdays along with Sundays, we’ll make a small adjustment to the formula. Here’s what it looks like:
=query(ArrayFormula(TO_DATE(row(indirect("E"&A1):indirect("E"&A2)))),"Select Col1 where dayOfWeek(Col1)=1 or dayOfWeek(Col1)=7")In this modified formula, the number 1 represents Sundays, and the number 7 represents Saturdays. The resulting output will display all the weekends between the provided start and end dates.
That’s it! You now have the power to effortlessly list all Sundays and weekends between any start and end date in Google Sheets.
Stay tuned for more amazing Google Sheets formulas and tricks on Crawlan.com. Happy Sheets-ing!
