Find the Last Friday (or Any Weekday) of a Month in Excel

To get the last Friday of a month in Excel, take the last day of the month with EOMONTH and step back to the nearest Friday with WEEKDAY and MOD. One formula does it, and changing a single number makes it work for the last Monday, Tuesday or any other weekday. This guide is for anyone who schedules paydays, month-end reports or recurring meetings that land on "the last Friday".

The last Friday of the month formula#

Use this formula, where A2 holds any date in the month you want:

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

It works in three steps:

1. EOMONTH(A2,0) returns the last day of the month in A2. The 0 means "this month", not a month before or after.
2. WEEKDAY(...,2) turns that last day into a weekday number. Return type 2 numbers the days Monday = 1 to Sunday = 7, so Friday is 5.
3. MOD(weekday-5,7) is how many days you need to go back to reach Friday. MOD returns the remainder after dividing by 7, so the result is always 0 to 6.

Subtract that number of days from the month's last day and you land on the last Friday. If the month ends on a Friday, MOD returns 0 and the answer is the last day itself.

Here is the formula on a few real months:

Look at November: 1-5 is -4, and MOD(-4,7) is 3, because Excel's MOD always returns a result with the same sign as the divisor. That is why the formula never steps forward into the next month. The EOMONTH function guide explains the months argument, and the WEEKDAY guide covers every return type.

How to find the last Monday, Wednesday or any weekday#

Replace the 5 with the number of the day you want. With return type 2, the numbers are:

So the last Monday of the month is:

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

For November 2026 this returns Mon 30 Nov 2026, the last day itself. For a reusable sheet, put the day number in a cell, say E1, and write -$E$1 instead of -5. Then one input changes every row.

You may also see this shorter version online:

=EOMONTH(A2,0)+1-WEEKDAY(EOMONTH(A2,0)+1-6)

It gives the same results, but it uses WEEKDAY's default numbering (Sunday = 1, so Friday = 6) and starts from the first day of next month. Pick one style and stick to it, because mixing the two numbering systems is the most common source of wrong days.

How to find the first, second or third Friday#

To find the first Friday, start from the first day of the month and step forward instead of back:

=EOMONTH(A2,-1)+1+MOD(5-WEEKDAY(EOMONTH(A2,-1)+1,2),7)

EOMONTH(A2,-1)+1 is the first day of the month: the last day of last month, plus one. Here MOD counts the days forward to Friday. Sunday 1 November 2026 has weekday 7, so MOD(5-7,7) is 5 and the first Friday is Fri 6 Nov 2026.

Every later Friday is a multiple of 7 days after that:

Be careful with a "fifth Friday": some months don't have one, so adding 28 can spill into the next month. Check it with MONTH(result)=MONTH(A2). The guide to the first and last day of the month uses the same building blocks for the first Monday.

How to find the last working day of the month#

If you need the last working day rather than a set weekday, WORKDAY is simpler:

=WORKDAY(EOMONTH(A2,0)+1,-1)

EOMONTH(A2,0)+1 is the first day of next month, and -1 asks WORKDAY for one working day before it. WORKDAY skips Saturdays and Sundays, so for November 2026 it returns Mon 30 Nov 2026, and for January 2027 it returns Fri 29 Jan 2027.

Add a list of public holidays as the third argument, for example =WORKDAY(EOMONTH(A2,0)+1,-1,$H$2:$H$10), and Excel skips those dates too. For more on stepping by months, see EDATE and EOMONTH explained.

What if the last Friday is a holiday?#

The last Friday of December 2026 is 25 December, which is a public holiday in many countries. The formula has no way to know that, so add a check. If your holidays are in H2:H10 and the last Friday is in B2:

=IF(COUNTIF($H$2:$H$10,B2)>0,B2-7,B2)

If the date appears in the holiday list, COUNTIF returns 1 and the formula moves one week earlier, to Fri 18 Dec 2026. If you would rather pay on the working day before the holiday, use =WORKDAY(B2,-1,$H$2:$H$10) for those rows, which gives Thu 24 Dec 2026.

How to list the last Friday of every month#

Put the first day of each month in column A and fill the formula down:

1. In A2, type =DATE(2027,1,1). In A3, type =EDATE(A2,1) and fill down to A13 for twelve months.
2. In B2, type the last Friday formula pointing at A2.
3. Select B2:B13 and fill down (Windows: Ctrl+D; Mac: ⌘D).

The A2 reference is relative, so each row uses its own month. Format column B as a date, or wrap the formula in TEXT for a label such as "Fri 29 Jan 2027": =TEXT(B2,"ddd d mmm yyyy"). To check how many days each month has before you plan around it, see how to count the days in a month.

Common mistakes#

Most wrong answers come from the weekday numbering, not from EOMONTH:

• Leaving out the 2 in WEEKDAY. Without it, Sunday is 1 and Friday is 6, so the formula finds the last Thursday. Always write WEEKDAY(date,2) when you use 5 for Friday.
• Using subtraction without MOD. EOMONTH(A2,0)-(WEEKDAY(EOMONTH(A2,0),2)-5) goes forward past the month end whenever the month ends on a Monday to Thursday. MOD fixes that.
• The result is a number like 46353. That is the date's serial number. Format the cell as a date (Windows: Ctrl+1; Mac: ⌘1).
• A2 holds text, not a date. EOMONTH returns #VALUE! if the date was typed or imported as text. Convert it first with DATEVALUE.
• EOMONTH shows a #NAME? error. Check the spelling: it is EOMONTH, with no space or dot.

If you need the next Friday after a given date rather than the last one in a month, the guide to finding the next weekday from a date has the forward version of this formula.

Practise it#

The exercise on this page uses real paydays: find the last Friday of November 2026 and fill it down for the next three months. Then practise WEEKDAY, EDATE and EOMONTH in a live spreadsheet, and if dates still feel strange, start with how Excel stores dates as numbers. You type each formula and it is checked instantly.