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 B2 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:

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

Excel find last business day of the month

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 the first date in the Date column of 1/25/2023 is on a Wednesday:

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:

How to Add Months to Date in Excel
How to Sort Months Chronologically in Excel
How to Calculate the Number of Months Between Dates in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *