Calculate the Nth Weekday of a Month

Many recurring dates are defined by weekday rather than by day number: the board meets on the second Tuesday of the month, payroll runs on the last Friday, a report goes out on the first Monday, and some public holidays fall on the fourth Thursday of November. Calendars, schedules and reminders built in Excel need these dates for every month of a year, and working them out by hand is tedious and easy to get wrong in months that start late in the week. pandas has frequencies built exactly for this, and Excel can compute them with a short formula. This page generates the second Tuesday and the last Friday of every month in 2026 with pandas, writes a single-date function for use anywhere, puts the matching Excel formula into a sheet and checks it against Python, and moves dates that fall on a public holiday. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl holidays

LibreOffice is used to recalculate the Excel formulas for the comparison; the formulas themselves work in Excel as written.

Step 1 — Every second Tuesday and last Friday of a year

import pandas as pd

second_tuesday = pd.date_range("2026-01-01", "2026-12-31", freq="WOM-2TUE")
last_friday = pd.date_range("2026-01-01", "2026-12-31", freq="LWOM-FRI")

meetings = pd.DataFrame({"Month": second_tuesday.strftime("%b %Y"),
                         "Second Tuesday": second_tuesday.strftime("%a %d"),
                         "Last Friday": last_friday.strftime("%a %d")})
print(meetings.to_string(index=False))

date_range accepts frequencies that describe exactly these rules. WOM-2TUE means week of month: the second Tuesday; the number can be 1 to 4 and the day any of MON to SUN. LWOM-FRI means last week of month: the last Friday. Each produces one date per month between the start and end dates, so a whole year's schedule is one line. The results move around as expected: the second Tuesday falls anywhere from the 8th to the 14th, and the last Friday from the 25th to the 31st, depending on which weekday the month starts on.

Step 2 — A function for one date, and the fifth-weekday question

from datetime import date, timedelta

def nth_weekday(year, month, weekday, n):
    """n-th weekday (Mon=0 … Sun=6) of a month; n = -1 for the last. None if the month has no such day."""
    if n > 0:
        first = date(year, month, 1)
        d = first + timedelta(days=(weekday - first.weekday()) % 7 + 7 * (n - 1))
        return d if d.month == month else None
    nxt = date(year + month // 12, month % 12 + 1, 1)
    last = nxt - timedelta(days=1)
    return last - timedelta(days=(last.weekday() - weekday) % 7)

print(nth_weekday(2026, 11, 3, 4), "— fourth Thursday of November")
print(nth_weekday(2026, 10, 4, 5), "— fifth Friday of October")
print(nth_weekday(2026, 2, 0, 5), "— fifth Monday of February")
print(nth_weekday(2026, 2, 4, -1), "— last Friday of February")

Outside pandas, or for a single date, the arithmetic is short. The first occurrence of a weekday is the first of the month plus however many days it takes to reach that weekday, modulo seven; each later occurrence adds a week. The last occurrence works backwards from the month's last day. The function returns None when the month does not have the requested occurrence — not every month has a fifth Monday — which matters for rules like the fifth Friday, used for occasional extra meetings or pay runs. October 2026 has one, on the 30th; February 2026, with exactly four weeks, has no fifth weekday of any kind. pandas' WOM frequencies stop at the fourth occurrence for that reason, so fifth-weekday rules need the function.

Step 3 — The same in Excel, checked against Python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Schedule"
ws.append(["Year", "Month", "Weekday (1=Sun … 7=Sat)", "N", "Date", "Python"])
cases = [(2026, m, 3, 2) for m in range(1, 13)]            # second Tuesday: Excel WEEKDAY numbers Tuesday as 3
for i, (y, m, wd, n) in enumerate(cases, start=2):
    ws.append([y, m, wd, n,
               f"=DATE(A{i},B{i},1)+MOD(C{i}-WEEKDAY(DATE(A{i},B{i},1)),7)+7*(D{i}-1)",
               nth_weekday(y, m, 1, n)])
    ws.cell(i, 5).number_format = ws.cell(i, 6).number_format = "ddd dd mmm yyyy"
ws["H1"], ws["H2"] = "Last Friday of the month in B2", "=EOMONTH(DATE(A2,B2,1),0)-MOD(WEEKDAY(EOMONTH(DATE(A2,B2,1),0))-6,7)"
ws["H2"].number_format = "ddd dd mmm yyyy"
wb.save("nth_weekday.xlsx")

soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true' nth_weekday.xlsx > /dev/null 2>&1
awk -F, 'NR>1 && $5!=$6 {bad++} END {print (NR-1) " months, " bad+0 " differences"}' nth_weekday.csv
sed -n '2p' nth_weekday.csv | cut -d, -f5-

The Excel formula follows the same logic as the function: DATE(year, month, 1) is the first of the month, WEEKDAY gives its weekday, MOD(target − weekday, 7) is the number of days to the first occurrence, and 7*(n−1) adds the extra weeks. The only trap is numbering: Excel's WEEKDAY counts Sunday as 1 and Saturday as 7 by default, while Python counts Monday as 0, so Tuesday is 3 in the sheet and 1 in the function. The last-Friday formula steps back from the end of the month, found with EOMONTH. Recalculating the sheet in LibreOffice and comparing the formula column with the Python column for all twelve months shows no differences, which is the reassurance needed before a schedule built either way is trusted.

Step 4 — When the date is a public holiday

import holidays

uk = holidays.country_holidays("GB", subdiv="ENG", years=2026)
first_mondays = pd.date_range("2026-01-01", "2026-12-31", freq="WOM-1MON")
adjusted = [d + pd.offsets.CustomBusinessDay(1, holidays=list(uk)) if d.date() in uk else d for d in first_mondays]
moved = [(a.date(), b.date(), uk.get(a.date())) for a, b in zip(first_mondays, adjusted) if a != b]
print(moved)

Rules like the first Monday often collide with bank holidays, which also tend to fall on Mondays. The usual policy is to move the event to the next working day, and a custom business-day offset that knows the holiday calendar does that in one step. In England in 2026 two first Mondays collide with bank holidays — Easter Monday on 6 April and the early May bank holiday on 4 May — so those months' events move to Tuesday 7 April and Tuesday 5 May. Other policies — skip the month, or move to the previous working day — are a different offset or a filter on the same list.

The reverse question: which occurrence is this date?

Sometimes the date is known and the question is the other way round — is this order date the second Tuesday of its month, or the last Friday? The occurrence number of any date is (day − 1) // 7 + 1: days 1 to 7 are the first occurrence of their weekday, 8 to 14 the second, and so on. A date is the last occurrence if adding seven days moves it into the next month.

dates = pd.Series(pd.to_datetime(["2026-03-10", "2026-03-27", "2026-03-31", "2026-11-26"]))
labels = pd.DataFrame({"date": dates.dt.strftime("%a %d %b"),
                       "occurrence": (dates.dt.day - 1) // 7 + 1,
                       "is last": (dates + pd.Timedelta(days=7)).dt.month != dates.dt.month})
print(labels.to_string(index=False))

This is useful for checking data against a rule — flagging meetings that were not held on the second Tuesday, or payments that missed the last Friday — and for building a week of month column for a report. Tuesday 10 March is the second Tuesday; Friday 27 March is the fourth Friday and also the last; Tuesday 31 March is the fifth and last Tuesday; and Thursday 26 November is the fourth Thursday and, because November 2026 has only four Thursdays, also the last. That double answer is why rules should say which they mean: the fourth Thursday and the last Thursday are the same day in some years and a week apart in others.

Common pitfalls

• Mixing weekday numbering. Excel WEEKDAY is Sunday = 1; Python is Monday = 0.
• Assuming a fifth occurrence exists. Check the result is still in the same month.
• Using WOM-5…. pandas stops at the fourth week; use a function for fifth-weekday rules.
• Ignoring holidays. Move dates that land on a holiday with a holiday-aware offset.
• Hard-coding a year's dates. Generate them so next year's schedule is one parameter.

Conclusion

For schedules built on weekdays, use pd.date_range with WOM-2TUE-style frequencies for the nth weekday and LWOM-FRI for the last one; use a small function when a single date or a fifth occurrence is needed; and, in a sheet, DATE plus MOD(target − WEEKDAY(first), 7) plus whole weeks, remembering that Excel numbers Sunday as 1. Check one against the other, and shift dates that land on public holidays.

Frequently asked questions

How do I get the second Tuesday of every month in pandas? Use pd.date_range(start, end, freq="WOM-2TUE").

How do I get the last Friday of every month in pandas? Use pd.date_range(start, end, freq="LWOM-FRI").

What is the Excel formula for the nth weekday of a month?=DATE(y,m,1)+MOD(w-WEEKDAY(DATE(y,m,1)),7)+7*(n-1), where w is the weekday number with Sunday as 1.

Does every month have a fifth Friday? No; only months where the fifth occurrence still falls inside the month, so check the result's month.

Related

• Up one level: Working with Dates and Times in Excel Data — dates in Excel data.
• Run a Report on the Last Business Day of the Month — business-day rules.
• Flag Public Holidays in Excel Data with Python — holiday calendars.
• Convert Text Months and Weekdays to Dates — weekday names into dates.