How to Find the Nth Weekday of a Month in Excel

To find the nth weekday of a month in Excel, such as the 4th Thursday or the 2nd Tuesday, you need one formula built from DATE, WEEKDAY and MOD. It finds the first matching weekday in the month, then adds 7 days for each week after that. This guide is for anyone scheduling meetings, pay dates or holidays that fall on "the third Monday" and wants a formula that works for any month and year.

The nth weekday of month formula#

Put the year, month, weekday and N in cells, then use:

=DATE(B1,B2,1)+MOD(B3-WEEKDAY(DATE(B1,B2,1),2),7)+7*(B4-1)

The result is 46352, which is Thursday 26 November 2026, US Thanksgiving. Excel stores every date as a whole number, so format the cell as a date with Ctrl+1 (Mac: ⌘1), or show it as text with =TEXT(B6,"ddd d mmm yyyy"), which returns "Thu 26 Nov 2026". The date basics lesson explains why dates are numbers.

How the formula works, piece by piece#

The formula has three parts, and each one answers a simple question.

1. Where does the month start? DATE(B1,B2,1) is the 1st of the month: Sunday 1 November 2026.
2. How many days until the first Thursday? WEEKDAY(DATE(B1,B2,1),2) gives the weekday of the 1st with Monday = 1, so Sunday is 7. MOD(4-7,7) turns −3 into 4. Four days after Sunday the 1st is Thursday 5 November.
3. How many weeks to add? 7*(B4-1) adds 0 days for the 1st Thursday, 7 for the 2nd, 14 for the 3rd and 21 for the 4th. Adding 21 to 5 November gives 26 November.

MOD is what makes this work. It always returns a number from 0 to 6, so you never step backwards into the previous month. If the 1st is already the weekday you want, MOD returns 0 and the 1st itself is the first match. The guide to the WEEKDAY function explains the return types in more detail.

Weekday numbers to use#

With WEEKDAY type 2, the numbers run from Monday to Sunday:

Type 2 is easier to remember than the default (type 1), which starts on Sunday = 1. Whatever you choose, use the same type for D and inside WEEKDAY, or every answer shifts by a day.

Examples: holidays and meeting dates#

The same formula handles any "nth weekday" rule. Here are some common ones, each checked against a calendar:

To list a date for every month, put the 1st of each month in column A (=DATE(2027,1,1) in A2, then =EDATE(A2,1) in A3 filled down). In B2, find the 2nd Tuesday:

=A2+MOD(2-WEEKDAY(A2,2),7)+7

Fill it down and you get 12 Jan, 9 Feb, 9 Mar and 13 Apr 2027 for the first four months. If you want a month name next to each date, see how to get the month name from a date.

A shorter formula with DATE and WEEKDAY#

You may also see this version, which uses the default WEEKDAY type (Sunday = 1):

=DATE(Y,M,1+7*N)-WEEKDAY(DATE(Y,M,8-D))

Here D runs from 1 (Sunday) to 7 (Saturday), so Thursday is 5. For Thanksgiving 2026, =DATE(2026,11,1+7*4)-WEEKDAY(DATE(2026,11,8-5)) also returns 26 November 2026. It's shorter but harder to read. Both formulas give the same answer for every month, so pick the one you can explain. Just don't mix their weekday numbers: Thursday is 4 in the first formula and 5 in this one.

How to find the last weekday of a month#

The last Monday or last Friday needs a different approach, because some months have four and some have five. Start from the last day of the month with EOMONTH and step back:

=EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0),2)-D,7)

For US Memorial Day (the last Monday of May), with any date in May 2027 in A2 and D = 1, the result is Mon 31 May 2027. EOMONTH(A2,0) is the month's last day. The EOMONTH function guide covers its arguments, and how to find the last Friday of the month walks through this formula in detail. For the first day of a month from any date, EOMONTH(A2,-1)+1 works too; see the first and last day of the month.

What if the month has no fifth weekday?#

Every month has at least four of each weekday, but only some have a fifth. In 2026, only January, May, July and October have five Fridays. If you ask for N = 5 in another month, the formula quietly returns a date in the next month.

Guard against it by checking the month of the result:

=IF(MONTH(B6)=B2,B6,"None this month")

For example, the "5th Friday" of February 2026 would land on 6 March, so the check returns "None this month". The guide on how many days are in a month explains why the count changes: a month has a fifth weekday only when it has 29 or more days and starts on the right day.

Practise it#

These formulas combine the date skills that job tests check most. Practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet: you type the formula and it checks it instantly. Then try the Thanksgiving exercise on this page, and change B3 and B4 to find any other weekday.