Exceljet

Quick, clean, and to the point

Get last weekday in month

Excel formula: Get last weekday in month
Generic formula 
=EOMONTH(date,0)+1-WEEKDAY(EOMONTH(date,0)+1-dow)
Explanation 

To get the last weekday in a month (i.e. the last Saturday, the last Friday, the last Monday, etc) you can use a formula based on the EOMONTH and WEEKDAY functions.

In the example shown, the formula in D5 is:

=EOMONTH(B5,0)+1-WEEKDAY(EOMONTH(B5,0)+1-C5)

How this formula works

First, this formula determines the first day of the next month *after* a given date. It does this my using EOMONTH to get the last day of the month, then adding one day:

=EOMONTH(B5,0)+1

Next, the formula calculates the number of days required to "roll back" to the last requested weekday in the month prior (i.e. the month of the original date):

WEEKDAY(EOMONTH(B5,0)+1-C5)

Inside WEEKDAY, EOMONTH is again used to get the first day of the next month. From this date, the value for day of week is subtracted, and the result is fed into WEEKDAY, which returns the number of days to roll back.

Last, the roll back days are subtracted from the first day of the next month, which yields the final result.

Other weekdays

In the general form of the formula at the top of the page, day of week is abbreviated "dow". This is a number between 1 (Sunday) and 7 (Saturday) which can be changed to get a different day of week. For example, to get the last Thursday of a month, set dow to 5.

Note: I ran into this formula in an answer on the MrExcel forum by Barry Houdini.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables