Summary

To get the last working day in a month, you can use the WORKDAY function together with the EOMONTH function. In the example, the formula in C4 is:

=WORKDAY(EOMONTH(B4,0)+1,-1)

Generic formula

=WORKDAY(EOMONTH(date)+1,-1)

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.

Related formulas

Excel formula: Get last day of month

Get last day of month

The 2nd argument ( months ) of the EOMONTH function allows you to get the last day of the month in future or past months. When you use zero for months, EOMONTH will return the last day of the month in the same month. To get the last day of the prior month, use: =EOMONTH(date,-1) To get the last day...
Excel formula: Get last weekday in month

Get last weekday in month

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...
Excel formula: Get nth day of week in month

Get nth day of week in month

First, this formula determines a "start date". The "first of month" is calculated with the DAY function here: =B5-DAY(B5)+1 Then n * 7 days is added the first of the month, to get a start date n weeks from the first of the month. In the generic form of the formula, n represents "nth". The next step...

Related functions

Excel WEEKDAY function

WEEKDAY Function

The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas to check the day of week.
Excel EOMONTH function

EOMONTH Function

The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EOMONTH to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for months to move forward in time, and a negative number...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions