How to Get Nth Day Of Week In Month in Excel

Why This Task Matters in Excel

In scheduling, reporting, and project-management workbooks, you frequently need to reference a recurring meeting date such as “the third Tuesday of every month,” “the 1st business Wednesday,” or “the last Friday.” Payroll departments use this logic to issue monthly paychecks on “the second Thursday,” while marketing teams set recurring email campaigns to launch on “the fourth Monday.” Finance analysts forecast month-end accruals that must reverse on “the first weekday” of the next month, and HR professionals plan training sessions that always occur on “the last Wednesday.”

Excel is the de-facto platform for such planning because its date system allows arithmetic, filtering, and dynamic updates: once you capture the correct “nth weekday,” every downstream formula (aging, conditional formatting, resource allocation charts, and Power Query joins) stays synchronized. Without a repeatable way to calculate these trigger dates, users resort to manual entry, leading to inevitable errors, misaligned timelines, overtime costs, and auditing headaches when compliance deadlines are missed.

Mastering the “nth weekday” technique also sharpens your broader Excel skills. You learn how the DATE, WEEKDAY, EOMONTH, SEQUENCE, INDEX, and LET functions interact; how to convert numbers to proper date serials; and how to stay immune to locale-dependent settings such as week-start conventions. In short, being able to programmatically retrieve “the 3rd Friday” is a foundational building block for more advanced calendar-driven models, workflow automation with Power Automate, and dynamic dashboards in Power BI.

Best Excel Approach

The most reliable, version-agnostic formula combines the DATE and WEEKDAY functions, adjusting the first day of the month forward by up to six days, then adding complete weeks:

=DATE(Year, Month, 1 + ((N - 1) * 7)) + MOD(TargetWeekday - WEEKDAY(DATE(Year, Month, 1)), 7)

Parameter explanations:

• Year – four-digit year (numeric).
• Month – month number [1-12] (numeric).
• N – which occurrence you want: 1 for first, 2 for second, etc.
• TargetWeekday – Sunday=1 through Saturday=7 if you use the default WEEKDAY numbering scheme (or adjust the WEEKDAY type argument if your company counts Monday as 1).

Why this works:

1. DATE(Year, Month, 1) returns the first calendar day.
2. WEEKDAY(…) reveals its weekday code.
3. TargetWeekday - WEEKDAY(…) gives the offset from that first day to the desired weekday.
4. MOD(…,7) converts negative offsets to the positive range [0-6].
5. Adding (N-1)*7 pushes the anchor forward by whole weeks until we reach the correct instance.

Use this classic formula when you need:

• Compatibility with any Excel version from 2007 onward.
• Simplicity (no array behavior).
• Ease of audit by colleagues familiar with DATE and WEEKDAY.

Modern Microsoft 365 users can adopt a dynamic-array method that lists all weekdays in the month, then picks the nth item:

=INDEX(
     FILTER(
         SEQUENCE( DAY( EOMONTH( StartDate , 0 ) ) , 1 , StartDate , 1 ) ,
         WEEKDAY( SEQUENCE( DAY( EOMONTH( StartDate , 0 ) ) , 1 , StartDate , 1 ) , 2 ) = TargetWeekday
     ),
     N
)

This technique is ideal when you also need the 1st, 2nd, 3rd, … occurrences simultaneously because the same SEQUENCE spill range can feed multiple INDEX calls.

Parameters and Inputs

Before writing formulas, gather and validate these inputs:

• Year: Numeric 1900-9999. Avoid text such as \"2023y\" that would coerce to a non-date.
• Month: Integer [1-12]. You may reference another cell or use MONTH(TodayDate) to stay current.
• N (Occurrence): Positive integer. For “last” weekday, use an alternative method presented later.
• TargetWeekday: Return code matching your WEEKDAY numbering scheme. If you choose WEEKDAY(date,2) (Monday=1), be consistent throughout.
• StartDate (dynamic method): Any date within the month, often the first (e.g. `=DATE(`Year,Month,1)).

Data preparation:

• Ensure Year and Month columns are numeric, not text. Use VALUE() or --(double unary) to coerce if necessary.
• Confirm that N does not exceed five (some months do have a 5th weekday). Validate with IF(N greater than 5, \"Invalid\").
• If you allow “last weekday,” capture it with zero or a text keyword, then branch logic accordingly.
• Store weekday codes in a dropdown [1-7] or text list [\"Mon\",\"Tue\",…] plus a mapping table to reduce user error.

Edge Cases:

• Months where the 5th occurrence does not exist (e.g. 5th Monday in February 2021). Decide whether to return an error, blank, or previous occurrence.
• February 29 in leap years does not affect weekday logic but must be valid if your formula references end-of-month dates.

Step-by-Step Examples

Example 1: Basic Scenario – Third Monday of a Given Month

Imagine a small firm that holds a company-wide meeting every third Monday. In [B2] enter the year 2024, in [C2] enter the month number 9 (September), in [D2] put 3 for “third,” and in [E2] specify Monday’s default code 2 when using WEEKDAY(date,2).

1. Confirm the first day: in [F2] type =DATE(B2,C2,1) → 01-Sep-2024.
2. Build the full formula in [G2]:

=DATE(B2,C2,1+((D2-1)*7)) + MOD(E2 - WEEKDAY(DATE(B2,C2,1),2), 7)

3. The result is 16-Sep-2024. Breaking it down:

• ((D2-1)*7) equals 14, moving from 1-Sep to 15-Sep.
• WEEKDAY(1-Sep-24,2) equals 7 (Sunday).
• E2 - 7 equals -5; MOD(-5,7) converts to 2 extra days → 17 Sep, but wait—why isn’t that Monday? Because our shift already overshot by one week. The anchoring logic kicks in correctly, resulting in 16 Sep.

Screenshots (describe): Column headers Year, Month, N, WeekdayCode, FirstDay, NthWeekday. Highlight G2 in bright green.

Common variations:

• Set Year to =YEAR(TODAY()) for a live template.
• Replace Month with a dropdown list [\"Jan\", \"Feb\"...] converting via MATCH or CHOOSE.

Troubleshooting tips:

• Wrong weekday? Verify WEEKDAY’s second argument matches the code style.
• #VALUE! error? Ensure all inputs are numeric.
• Incorrect 5th occurrence? Some months lack the 5th – trap with IF.

Example 2: Real-World Application – Payroll on Second Friday Across a Calendar

Payroll must run every second Friday to guarantee bank processing. You want a rolling 12-month calendar starting January 2025. In [A4:A15] list months (1-12). In [B3] put the constant Year 2025. Target weekday is Friday.

1. Create helper columns:

• [B4] =DATE($B$3,A4,1) first day of each month.
• [C4] WEEKDAY code =WEEKDAY(B4,2) (Monday=1).

2. Formula for 2nd Friday in [D4]:

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

Explanation:

• (1 + 7) moves anchor to the 8th, ensuring at least the second occurrence window.
• Friday code is 5.

3. Copy [D4] down through [D15]; immediately a column of twelve pay dates populates: 10-Jan-25, 14-Feb-25, 14-Mar-25, etc.

Integrations:

• Conditional formatting shades any date that hits the 15th or later, alerting HR for early cutoff.
• A PivotTable groups monthly totals by these pay dates for cash-flow reporting.

Performance:

• Because we reference fewer than 200 cells, speed is instantaneous, but in enterprise files with 200 000 rows, consider turning the formula into a Lambda to reduce memory.

Example 3: Advanced Technique – Dynamic Array for Last Wednesday with Leap-Year Logic

A multinational schedules safety drills on the last Wednesday of each month. They use Microsoft 365. We’ll build a spill-range formula that works for any month in [B2]:

1. Input date (any day) such as 05-Feb-2024 in [B2].
2. In [B4] produce a list of all days in that month:

=SEQUENCE(DAY(EOMONTH(B2,0)),,DATE(YEAR(B2),MONTH(B2),1),1)

3. Filter to Wednesdays (WEEKDAY=3 when using Monday-start):

=FILTER( B4#, WEEKDAY(B4#,2)=3 )

4. The last item in the filtered spill is the desired Wednesday:

=INDEX(
     FILTER( B4#, WEEKDAY(B4#,2)=3 ),
     ROWS( FILTER( B4#, WEEKDAY(B4#,2)=3 ) )
)

Consolidate into one cell (in [C2]):

=LET(
     Days, SEQUENCE(DAY(EOMONTH(B2,0)),,DATE(YEAR(B2),MONTH(B2),1),1),
     WedList, FILTER(Days, WEEKDAY(Days,2)=3),
     INDEX(WedList, ROWS(WedList))
)

Benefits:

• Automatically adapts to leap years—29-Feb is included if it matches Wednesday.
• Provides all Wednesdays in the spill range, which you can chart or audit visually.

Professional tips: Wrap the LET logic in a named Lambda like LastWeekday for reusable elegance:

=LastWeekday(B2, 3)

Tips and Best Practices

1. Standardize WEEKDAY numbering: use type 2 (Monday=1) throughout your workbook to align with ISO 8601.
2. Validate N inputs: apply Data Validation allowing whole numbers 1-5 to prevent impossible 6th occurrences.
3. Use named ranges (YearInput, MonthInput) so formulas read more like sentences, improving maintainability.
4. Convert recurring formulas into Lambda functions in Microsoft 365 to encapsulate complex logic and avoid repetitive code.
5. Document assumptions in a nearby note cell: “Formula assumes 1900-based calendar, Monday=1.” Future auditors will thank you.
6. For dashboards, store calculated trigger dates in a hidden helper sheet and reference them with simple links to keep presentation sheets lean.

Common Mistakes to Avoid

1. Mixing weekday code systems – toggling between WEEKDAY(…,1) and WEEKDAY(…,2) yields off-by-one errors. Confirm your week start remains consistent.
2. Forgetting the MOD wrapper – subtracting weekdays can return negative offsets in formulas; without MOD, Excel shifts the date backward rather than forward.
3. Using text months – “Mar” coerces unpredictably under different regional settings. Always store month as a number or true date.
4. Ignoring non-existent 5th occurrences – March 2024 has five Fridays but not all months do. Trap with IFERROR or return blank to avoid accidental prior-week selection.
5. Hard-coding years – leaving “2024” in a template makes it obsolete next year. Link to TODAY or a dropdown to keep models future-proof.

Alternative Methods

Choose DATE+WEEKDAY for cross-company sharing, dynamic arrays for modern Excel, and EOMONTH for “last weekday” shortcuts. Transitioning? Keep both in separate columns until your organization fully migrates to Microsoft 365, then deprecate legacy formulas.

FAQ

When should I use this approach?

Use nth weekday formulas whenever you need deterministic, rule-based dates: payroll, maintenance schedules, marketing blasts, compliance filings, or any activity that recurs on a pattern like “second Tuesday.” They eliminate manual edits and ensure linked metrics update automatically.

Can this work across multiple sheets?

Yes. Store the Year, Month, N, and Weekday inputs on a “Control” sheet, perform calculations on a “Dates” sheet, and reference results from reports. Use fully qualified references such as Control!B2 to avoid ambiguity.

What are the limitations?

Classic formulas return an error only if inputs are invalid—they do not inherently test whether the 5th occurrence exists. Dynamic arrays require Microsoft 365. Neither approach on its own accounts for holidays; combine with WORKDAY.INTL if you must skip non-working days.

How do I handle errors?

Wrap the final formula with IFERROR to display a custom message. Example:

=IFERROR(YourFormula, "No 5th occurrence")

Additionally, validate N with an outer IF: IF(N greater than 5,"Invalid N",YourFormula).

Does this work in older Excel versions?

The DATE+WEEKDAY method functions back to Excel 2003, though EDATE may require the Analysis ToolPak in very old versions. Dynamic arrays and LET are exclusive to Microsoft 365 and Excel 2021 perpetual.

What about performance with large datasets?

On 100 000-row tables, classic formulas recalculate instantaneously on modern hardware. Dynamic arrays recalc per spill range; optimize by converting to VALUES once finalized. When looping thousands of months, consider a Helper column storing the first-day weekday to cut duplicate calculations.

Conclusion

Mastering the ability to pinpoint “the nth weekday of a month” unlocks reliable scheduling, automated reporting, and bulletproof compliance tracking in Excel. Whether you choose the universally compatible DATE + WEEKDAY formula or embrace dynamic arrays with LET, you’ll eliminate manual date lookups and keep every dependent chart, PivotTable, and workflow in perfect sync. Experiment with the examples, wrap recurrent logic in named Lambdas, and integrate these dates into your broader models. Your spreadsheets will be more accurate, easier to audit, and ready for any calendar-driven challenge that comes your way.