Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to list every Monday in a month in Excel and Google Sheets
Heads up: Excel and Google Sheets do this differently.
Excel
=IFERROR(SMALL(IF(WEEKDAY(DATE(A2,B2,SEQUENCE(31)),2)=1,DATE(A2,B2,SEQUENCE(31))),SEQUENCE(6)),"")Google Sheets
=FILTER(DATE(A2,B2,SEQUENCE(31)),WEEKDAY(DATE(A2,B2,SEQUENCE(31)),2)=1,DATE(A2,B2,SEQUENCE(31))<=EOMONTH(DATE(A2,B2,1),0))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Year | Month |
| 2025 | 6 |
=IFERROR(SMALL(IF(WEEKDAY(DATE(A2,B2,SEQUENCE(31)),2)=1,DATE(A2,B2,SEQUENCE(31))),SEQUENCE(6)),"")→
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter the target year in A2 (e.g. 2025) and the month number in B2 (e.g. 6 for June).
- 2In Google Sheets enter =FILTER(DATE(A2,B2,SEQUENCE(31)),WEEKDAY(DATE(A2,B2,SEQUENCE(31)),2)=1,DATE(A2,B2,SEQUENCE(31))<=EOMONTH(DATE(A2,B2,1),0)). The EOMONTH guard removes days beyond the month end.
- 3In Excel 365 enter the same FILTER formula. In Excel 2019 and earlier, use the SMALL/IF pattern: select 6 cells, enter =IFERROR(SMALL(IF(WEEKDAY(DATE(A2,B2,SEQUENCE(31)),2)=1,DATE(A2,B2,SEQUENCE(31))),SEQUENCE(6)),"") and confirm with Ctrl+Shift+Enter.
- 4Format the result cells as dates (Ctrl+1 in Excel; Format > Number > Date in Sheets) so serial numbers display as readable dates.
- 5Verify: a month with 5 Mondays returns exactly 5 dates; a month with 4 returns 4 with no errors or overflow into the next month.
Tips
- WEEKDAY mode 2 assigns 1 to Monday through 7 to Sunday, making the Monday filter a clean =1 comparison. The default mode 1 starts at Sunday=1 and makes the logic less obvious.
- SEQUENCE(31) always produces 31 day numbers regardless of month length — dates beyond the month end overflow into the next month, so the EOMONTH guard is essential.
- To list a different weekday, change the =1 comparison: 2=Tuesday, 3=Wednesday, 5=Friday, 7=Sunday (all using WEEKDAY mode 2).
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why do some dates fall in the wrong month?
SEQUENCE(31) generates day numbers up to 31 regardless of how many days are in the month. Days beyond the month end overflow into the next month. The EOMONTH guard (DATE(...)<=EOMONTH(...)) removes them.
Can I list Mondays across a full year in one formula?
Yes — generate all 365 days with SEQUENCE(365) starting from January 1, then filter by WEEKDAY=1. Combine with a start date instead of separate year/month inputs.