# How to find the last Friday of the month in Excel

Step back from the month end to the last Friday: `=EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-6,7)`.

**Excel:** `=EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-6,7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Pay month | Last Friday |
| 2026-01-05 |  |
| 2026-02-17 |  |
| 2026-04-01 |  |
| 2026-07-22 |  |
| 2026-10-10 |  |
| 2026-11-30 |  |

Formula in B2, filled to B7: `=EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-6,7)`

Result: 1/30/2026; 2/27/2026; 4/24/2026; 7/31/2026; 10/30/2026; 11/27/2026

## How it works

`EOMONTH(A2,0)` is the last day of the month. WEEKDAY gives 6 for Friday, so `MOD(WEEKDAY(last)-6,7)` is how many days the month end is past a Friday: 0 on a Friday, 1 on a Saturday, 6 on a Thursday. Subtracting it steps back to the last Friday.

## Other ways

- `=EOMONTH(A2,0)+1-WEEKDAY(EOMONTH(A2,0)+2)`: A shorter classic that works for Friday only.
- `=LET(last,EOMONTH(A2,0),last-MOD(WEEKDAY(last)-6,7))`: The same with LET, so the month end is calculated once.

## 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.

## Questions

**How do I get the last Monday, like Memorial Day?**

`=EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-2,7)`. For May 2026 that is May 25.

**How do I get the last business day instead?**

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/last-friday-of-the-month