How to find the nth weekday of a month in Excel

The formula for any nth weekday

The sheet is a list of recurring dates: each row has a year, a month, which occurrence (2 for "second") and the weekday as a number. Keeping these in cells means one formula serves every row; you just fill it down.

Read it in three parts. DATE(B2,C2,1) is the 1st of the month, Sunday 1 November 2026. MOD(E2-WEEKDAY(...),7) is how many days to move forward to the first Tuesday: WEEKDAY gives 1 for that Sunday, Tuesday is 3, so it moves 2 days to 3 November. MOD keeps the answer between 0 and 6, so if the 1st is already a Tuesday it moves 0 days. Then 7*(D2-1) adds whole weeks: 7 days for the second Tuesday.

Filled down, the same formula gives Thursday 26 November 2026 for US Thanksgiving (4th Thursday) and Friday 18 December 2026 for the craft market (3rd Friday). The result is a real date; format the cell as a date if it shows a number such as 46336.

Get the weekday number right

This is the usual mistake. With no second argument, WEEKDAY numbers the days Sunday 1, Monday 2, Tuesday 3, through Saturday 7. Many people type 2 for Tuesday because it is the second working day. In this formula that quietly returns Monday 9 November instead, with no error to warn you.

If you prefer Monday as day 1, give WEEKDAY a return type of 2 (Monday 1 to Sunday 7) and use the matching numbers, as below. Either system works; mixing them does not. Label the weekday column with its system, like "Weekday (Sun=1)" here, so whoever edits the sheet next uses the same numbers.

The last weekday of the month

"Last Friday" is not the same as "4th Friday". January 2027 has five Fridays, so the 4th is the 22nd, a week before the last. Memorial Day (last Monday of May) has the same trap: the 4th Monday of May 2027 is the 24th, but the last is the 31st.

Work backwards from the month end instead. EOMONTH(date,0) is the last day of the month, Sunday 31 January 2027. MOD(WEEKDAY(month end)-E5,7) is how many days to step back to reach a Friday: 2 here. If the month ends on the right weekday, it steps back 0 days, which is why Memorial Day 2027 comes out as Monday 31 May.

For the second-to-last, subtract 7 more. To pick nth or last from the same table, test the Nth column: =IF(D5="last",last formula,nth formula). Excel only works out the branch it needs, so the word "last" in D5 does not break the nth formula.

When the 5th weekday does not exist

Some months have five Fridays, most do not. February 2027 starts on a Monday, so its Fridays are the 5th, 12th, 19th and 26th. Ask the formula for the 5th Friday and it adds four weeks to 5 February and returns Friday 5 March 2027, in the wrong month, with no error.

Check the month of the answer. If MONTH(result) is not the month you asked for, the date does not exist. The LET version below names the result once so the check stays readable. If your rule is really "the 5th Friday, or the last one when there is no 5th", use the last-weekday formula instead.

Shorter alternatives: WORKDAY.INTL and the classic formula

WORKDAY.INTL (Excel 2010 and later) can do it by treating every day except your weekday as a weekend. The weekend argument is seven 1s and 0s for Monday to Sunday, where 0 is a working day. "1011111" leaves only Tuesday, so =WORKDAY.INTL(DATE(B2,C2,1)-1,D2,"1011111") counts D2 Tuesdays forward from the day before the 1st and returns Tuesday 10 November 2026. Counting -1 back from the day after the month end gives the last one: =WORKDAY.INTL(EOMONTH(DATE(B5,C5,1),0)+1,-1,"1111011") returns Friday 29 January 2027.

You can pass a holiday list as the fourth argument, and WORKDAY.INTL will skip a holiday Tuesday and move to the next Tuesday. That can be what you want for a meeting, but it is no longer strictly the "2nd Tuesday".

On forums you will also see =DATE(Y,M,1+7*N)-WEEKDAY(DATE(Y,M,8-W)). It gives the same dates as the MOD formula (we checked every weekday for 2026 to 2028) and uses the same Sunday=1 numbers, but it is harder to explain to the next person who opens the file.

A year of meeting dates in one formula

In Excel 365 or 2021, put SEQUENCE(12) where the month goes and the formula spills all twelve dates. This one lists the second Tuesday of every month in 2027, ready for a board calendar or an Outlook invite list.

The last-Friday version works the same way: =EOMONTH(DATE(2027,SEQUENCE(12),1),0)-MOD(WEEKDAY(EOMONTH(DATE(2027,SEQUENCE(12),1),0))-6,7) runs from Fri 29 Jan to Fri 31 Dec. Format the results as dates, or keep the TEXT wrapper if you only need labels.

To go the other way and ask "which Tuesday is this?", use =INT((DAY(A2)-1)/7)+1. It returns 4 for 26 November 2026 (the 4th Thursday) and 5 for 29 January 2027. A date is the last of its weekday in the month when =MONTH(A2+7)<>MONTH(A2) is TRUE. Both are handy in conditional formatting rules.