How to find the last Friday of the month in Excel

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

In Google Sheets

The same formula works in Google Sheets: =EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-6,7). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Common mistakes

• Without MOD, months that end on a Wednesday or Thursday give a negative step and a date in the next month.
• For another weekday, change the 6: 1 is Sunday, 2 Monday, up to 7 for Saturday.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Related answers

• How to find the 3rd Wednesday of a month in ExcelFind the first matching weekday, then add 7 days per extra week: =LET(first,EOMONTH(A2,-1)+1,first+MOD($E$1-WEEKDAY(first),7)+7*($E$2-1)).
• How to find the first Monday of the month in ExcelTake the first of the month and add the days to the next Monday: =LET(first,EOMONTH(A2,-1)+1,first+MOD(2-WEEKDAY(first),7)).
• How to calculate the week of the month from a date in ExcelOffset the day number by the weekday of the 1st, then divide by 7: =INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1.
• How to get the date of the next Friday in ExcelAdd the days to the next Friday: =A2+7-MOD(WEEKDAY(A2)-6,7), with the weekday number (6 for Friday) in a cell if it changes.
• How to get the end date of the quarter in ExcelMove forward by the months left in the quarter and take the month end: =EOMONTH(A2,MOD(3-MONTH(A2),3)).
• How to add months to a month-end date and stay at the end of the month in ExcelUse =EOMONTH(A2,B2). It adds the months and always returns the last day of the resulting month.

Functions used: EOMONTH · MOD · WEEKDAY

Lessons: EDATE, EOMONTH & NETWORKDAYS (Pro)