Explanation
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 .
Explanation
Working from the inside out, the EOMONTH function gets the last day of month of any date. To this result, we add 1, which results in the first day of the next month.
This date goes into WORKDAY function as the “start date”, along with -1 for “days”. The WORKDAY function automatically steps back 1 day, taking into account any weekends. The result is the last workday of the month.
Holidays
To get the last working day of the month, taking into account holidays, just add the range that contains holiday dates to the formula like this:
=WORKDAY(EOMONTH(B4,0)+1,-1,holidays)
Custom weekends
The WEEKDAY function assumes weekends are Saturday and Sunday. If you need to customize weekend days, you can use the WEEKDAY.INTL function.
