How to List Nth Weekdays Of The Month in Excel

Why This Task Matters in Excel

Scheduling is at the heart of nearly every business process. Payroll must hit bank accounts on the second Friday, executive meetings happen on the first Monday, and marketing reports are emailed on the third Wednesday of each month. Being able to list the nth weekday of every month turns these recurring events into precise, error-free timelines that stakeholders can rely on.

Imagine running a payroll department. Employees expect payment on time, and banking cut-off times mean you cannot rely on “around the middle of the month.” The second Friday rule exists because it avoids holidays early in the month yet keeps cash-flow predictable. If you miscalculate just once, you create panic for hundreds of employees. Similar stakes exist for regulatory filings that must occur on the fourth business day, or maintenance teams that perform inspections each first Tuesday.

Excel excels (pun intended) at this problem because:

1. Its date serial number system allows straightforward arithmetic—dates are just numbers behind the scenes.
2. Dynamic array formulas such as SEQUENCE, FILTER, and SORT let you generate entire schedules with a single spill formula in modern Excel.
3. Backward-compatible functions—DATE, WEEKDAY, and EOMONTH—work in versions as old as Excel 2007, so you can build robust, shareable workbooks.

Failing to master the nth weekday technique leads to tedious manual tracking, misaligned calendars, and costly compliance errors. Conversely, automating these calculations links seamlessly with features like conditional formatting, data validation, Power Query, and PivotTables. Your skill in generating accurate recurring dates becomes a foundational building block in dashboards, project plans, and workflow automations.

Best Excel Approach

The most flexible approach is a dynamic array formula that builds an entire month of dates, filters for weekdays, then extracts the nth item. This solution is concise, easy to audit, and adapts instantly when you change the month, year, or desired occurrence.

Recommended modern Excel formula (Office 365 or Excel 2021):

=LET(
    yr,   $B$2,            /* Year input                        */
    m,    $B$3,            /* Month number 1-12                */
    n,    $B$4,            /* Nth occurrence: 1st, 2nd, 3rd…   */
    wk,   $B$5,            /* Target weekday: 1-7 (Mon=1)      */
    first, DATE(yr,m,1),   /* First day of selected month      */
    last,  EOMONTH(first,0),
    dates, SEQUENCE(last-first+1,,first,1),
    weekdays, FILTER(dates, WEEKDAY(dates,2)=wk),
    INDEX(weekdays, n)
)

Why this is best:

• One cell, one answer or spill list – no helper columns needed.
• LET names each intermediate step, improving readability and performance.
• WEEKDAY(date,2) returns Monday=1 … Sunday=7, an intuitive mapping for business users.
• The approach scales: remove the final INDEX and the formula spills the complete list of all weekdays in the chosen month.

Alternative (works in Excel 2019 and earlier, single result):

=DATE($B$2,$B$3,1)
 +MOD($B$5-WEEKDAY(DATE($B$2,$B$3,1),2),7)
 +7*($B$4-1)

This classic arithmetic version runs efficiently in older workbooks but lacks the dynamic spill capabilities of the modern method.

Parameters and Inputs

1. Year (yr) – Integer such as 2024. Must be a valid four-digit year; avoid two-digit abbreviations.
2. Month (m) – Integer 1 through 12. You can allow text months with MONTH(DATEVALUE(text&\" 1\")) if preferred, but numeric is simpler and faster.
3. Nth occurrence (n) – Positive integer typically 1-5. Use data validation to restrict entries and prevent impossible requests, such as a 6th Monday.
4. Weekday (wk) – Integer 1-7 aligned with WEEKDAY(,2) where Monday=1. Display a friendly drop-down [“Mon”,“Tue”,…] that feeds these numbers.
5. Data preparation – Ensure the inputs reside in clearly labeled cells ($B$2:$B$5 in this tutorial) so the formula references remain stable.
6. Edge cases – If n exceeds the number of weekday occurrences in that month, the modern formula returns a #NUM! error. You can trap this with IFERROR or a custom message:

   =IFERROR(finalResult,"No such occurrence")
   

Step-by-Step Examples

Example 1: Basic Scenario – Find the 3rd Wednesday of March 2024

1. Set up inputs

   • B2: 2024
   • B3: 3
   • B4: 3
   • B5: 3 (Wednesday because Monday=1)

2. Enter the recommended LET formula in D2. Immediately, the cell returns 20-Mar-2024.

3. Why it works

   • SEQUENCE spills every date from 01-Mar-2024 to 31-Mar-2024.
   • WEEKDAY(,2)=3 filters only Wednesdays: [6-Mar-2024, 13-Mar-2024, 20-Mar-2024, 27-Mar-2024].
   • INDEX(...,3) picks the 3rd item, 20-Mar-2024.

4. Variations

   • Change n to 1 to get the first Wednesday (6-Mar-2024).
   • Switch the month to 2 (February) and Excel instantly re-calculates.

5. Troubleshooting

   • If you accidentally set n to 5, February 2024 has only four Wednesdays; Excel throws #NUM!. Wrap the final INDEX in IFERROR to display “None”.

Example 2: Real-World Application – Generate a Payroll Calendar (1st and 3rd Friday for 2025)

Human Resources must upload payroll files on every 1st and 3rd Friday. We need a two-column list covering the entire year.

1. Data context

   • Year: 2025 (cell B2)
   • Occurrences: 1 and 3 (row headers in column A)
   • Weekday: Friday = 5 (B5)

2. Create a month list in C1:C12 using

   =SEQUENCE(12,,1,1)
   

3. Place the LET formula in D2, but convert the month reference to use the spill value:

   =LET(
       yr, $B$2,
       n,  $A$2,      /* 1st or 3rd from column A           */
       wk, $B$5,      /* Friday                             */
       m,  SEQUENCE(12),
       first, DATE(yr,m,1),
       last,  EOMONTH(first,0),
       dates, SEQUENCE(last-first+1,,first,1),
       weekdays, FILTER(dates, WEEKDAY(dates,2)=wk),
       INDEX(weekdays, n)
   )
   

   Because m is a spilled array [1,2,3…12], almost every variable is now a vector. The formula spills a 12-row schedule listing either the 1st or 3rd Friday for each month.

4. Copy the formula in D2 down one row. Change A3 from 1 to 3, instantly yielding the 3rd Friday dates. You have a ready-to-use table:

5. Integration

   • Use TEXT(D2#, \"dddd, dd-mmm\") in a helper column for presentation.
   • Connect Power Query to the final range to push the schedule into a SharePoint list.

6. Performance notes
   Twelve months × 31 possible days means only 372 date serials; modern Excel evaluates instantly even on modest hardware.

Example 3: Advanced Technique – Custom LAMBDA Function

Power users with Microsoft 365 can encapsulate all logic into a reusable function.

1. Define the function:
   Formulas ➜ Name Manager ➜ New Name

   • Name: NthWeekday
   • Refers to:

   =LAMBDA(yr,m,n,wk,
     LET(
       first, DATE(yr,m,1),
       last,  EOMONTH(first,0),
       dates, SEQUENCE(last-first+1,,first,1),
       weekdays, FILTER(dates, WEEKDAY(dates,2)=wk),
       INDEX(weekdays,n)
     )
   )
   

2. Call the function anywhere:

   =NthWeekday(2026,7,4,1)   /* 4th Monday July 2026 */
   

   It returns 27-Jul-2026.

3. Edge case handling
   Extend the lambda with IFERROR to gracefully return blank if the nth weekday does not exist.

4. Why use LAMBDA

   • Centralized logic—update once, propagate to hundreds of sheets.
   • Documentation—arguments names appear as tooltips.
   • Composability—nest inside other lambdas such as a YearlySchedule function.

5. Performance optimization
   LAMBDA caches intermediate steps within LET, avoiding recalculations. For larger models (say, 20 years of schedules), this matters.

Tips and Best Practices

1. Validate inputs with Data ➜ Data Validation to limit n between 1 and 5 and month between 1 and 12.
2. Use DATE(year,month,1) instead of hard-coding serial numbers; it avoids regional settings conflicts.
3. Keep weekday numbering consistent – pick WEEKDAY(,2) throughout so Monday=1. Mixing return types is a common source of off-by-one errors.
4. Spill consciously – dynamic arrays recalculate whenever any referenced cell changes. Keep volatile functions like TODAY outside your weekday formulas to minimize recalculation storms.
5. Name your ranges – Year_input, Month_input, etc. Using these names inside LET makes formulas self-documenting.
6. Wrap in IFERROR for end-user polish. A blank or custom text is friendlier than #NUM!.

Common Mistakes to Avoid

1. Wrong WEEKDAY mode – Using WEEKDAY(,1) where Sunday=1 breaks Monday-centric logic. Always specify the second argument explicitly.
2. Requesting impossible occurrences – A fifth Thursday exists only in some months. Neglecting error trapping leads to confusing #NUM! results. Compare n to COUNTA(FILTER(...)) before indexing.
3. Mixed date and text types – Copy-pasting inputs from other systems may insert text dates. Wrap them with DATEVALUE or check with ISTEXT.
4. Hard-coding year or month – Embedding 2024 in the formula makes maintenance painful. Reference input cells or use a Named Range.
5. Forgetting absolute references when copying formulas. Lock your input cells with dollar signs, or a dragged formula will start pointing to the wrong rows and output nonsense.

Alternative Methods

Choose the arithmetic formula when collaborating with users on older versions; pick the LET method for modern collaboration in Microsoft 365; deploy Power Query when integrating with databases or when you require bulk calendar structures.

FAQ

When should I use this approach?

Use it whenever you have a recurring schedule based on weekday order rather than a fixed calendar date—payroll, meetings, compliance filings, or promotional campaigns.

Can this work across multiple sheets?

Yes. Reference the input cells with sheet prefixes (e.g., Schedule!B2) or wrap the logic in a LAMBDA stored in the workbook’s Name Manager. That single function can then be called from any sheet without duplication.

What are the limitations?

The formula can return a #NUM! error if n exceeds the available weekdays, and older Excel versions lack SEQUENCE and LET. Large spill ranges can grow workbook size if copied repeatedly.

How do I handle errors?

Wrap the final result in IFERROR or IFNA:

=IFERROR(NthWeekday(yr,m,n,wk),"No such day")

You can also pre-validate:

=IF(n>COUNTA(WEEKDAYS),"Not available",INDEX(...))

Does this work in older Excel versions?

The arithmetic MOD-based formula works down to Excel 2007. Modern dynamic arrays require Office 365 or Excel 2021. Always test in the version used by your recipients.

What about performance with large datasets?

Even dynamic arrays recalculate quickly because they use in-memory vectorized operations. If you are generating decades of schedules, consider moving the heavy lifting to Power Query or defining a single LAMBDA that spills once rather than copying formulas across thousands of cells.

Conclusion

Mastering the ability to list nth weekdays empowers you to automate everything from payroll calendars to executive meeting timelines. The modern LET-based formula provides clarity and flexibility, while fallback arithmetic ensures backward compatibility. Add robust validation, thoughtful error handling, and perhaps a custom LAMBDA, and you’ll never again scramble to count Fridays on a wall calendar. Continue exploring related skills—dynamic arrays, Power Query, and date math—to round out your scheduling toolkit and elevate your overall Excel proficiency.