Dates & Time
Get Last Specific Weekday of Month (Last Friday, Monday, or Thursday)
Calculate the exact date of the last Friday, Monday, or any target weekday (1=Sun to 7=Sat) in a month using EOMONTH(date, 0) - MOD(WEEKDAY(EOMONTH(date, 0)) - target_dow + 7, 7).
Anchor monthly payroll runs, futures expirations, and closing Fridays!
How do you find the Last Friday (or Last Monday) of any calendar month—whether that month has 28, 29, 30, or 31 days and ends on a Saturday, Sunday, or Tuesday? Start at the month's final calendar day EOMONTH(B2, 0), inspect its day-of-week code WEEKDAY(EOMONTH(B2, 0)) (where Sunday = 1 ... Friday = 6, Saturday = 7), and step backward 0 to 6 days using modulo-7 arithmetic!
Specifically, MOD(WEEKDAY(EOMONTH(B2, 0)) - Target_DOW + 7, 7) computes the exact backward offset in [0, 6]: in October 2026, October 31 falls on Saturday (7), so stepping back MOD(7 - 6 + 7, 7) = 1 day lands on Friday, 2026-10-30! In November 2026, November 30 falls on Monday (2), so stepping back MOD(2 - 6 + 7, 7) = 3 days lands on Friday, 2026-11-27!
| Input Date (B2) | Month End EOMONTH(B2, 0) | Target DOW (1=Sun..7=Sat) | Last Target Weekday Date |
|---|---|---|---|
| 2026-10-12 | 2026-10-31 (Saturday = 7) | 6 (Friday) | 2026-10-30 (Back 1 day) |
| 2026-11-10 | 2026-11-30 (Monday = 2) | 6 (Friday) | 2026-11-27 (Back 3 days) |
| 2026-11-10 | 2026-11-30 (Monday = 2) | 2 (Monday) | 2026-11-30 (Back 0 days!) |
Last Target Weekday of Current Month (Formatted ISO Date)
Validates that target weekday B3 is an integer in 1..7 (1=Sunday through 7=Saturday), then subtracts the modulo-7 offset from EOMONTH(B2, 0).
=IF(OR(B3 < 1, B3 > 7), "Invalid Weekday Code (Use 1=Sun..7=Sat)", TEXT(EOMONTH(B2, 0) - MOD(WEEKDAY(EOMONTH(B2, 0)) - B3 + 7, 7), "yyyy-mm-dd"))Days Remaining Until Month's Last Target Weekday
Returns signed calendar days from B2 until that month's final target weekday (negative once passed).
=(EOMONTH(B2, 0) - MOD(WEEKDAY(EOMONTH(B2, 0)) - B3 + 7, 7)) - B2Try the example
Use the original values or change them to test your understanding.