---

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.

Each of the numeric values in column B will be converted to a date format:

Column B shows the last business day of the month that the date in column A belongs to.

For example, we can look at a calendar to see that 1/4/2022 is on a Tuesday:

The last business day of the month for January is on Tuesday the 31st.

Thus, our formula returns 1/31/2023.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel: