How to Find Last Business Day of Month in Excel

You can use the following formula to find the last business day of the month for a given date in Excel:

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

This formula will return the last business day of the month for the date in cell A2.

This formula works by using three simple steps:

First, the EOMONTH function finds the last day of the month for the date in cell A2
Then we add 1, which results in the first day of the next month.
Lastly, we use the WORKDAY function to go back one workday (or “business” day), which is the last business day of the month for the date in cell A2.

The following step-by-step example shows how to use this formula in practice.

Step 1: Enter the Dataset

First, let’s enter the following column of date values into Excel:

Step 2: Calculate Last Business Day of Month

Next, we will type the following formula into cell CB2 to find the last business day of the month for the date in cell A2:

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

We can then click and drag this formula down to each remaining cell in column B:

Step 3: Convert Numeric Values to Date Format

By default, the date values are shown as numeric values.

Note: The numeric values represent the number of days since 1/1/1900.

To convert these numeric values to dates, we can highlight the cell range B2:B13 and then click the Number Format dropdown menu on the Home tab and then click Short Date: