Table of Contents
The importance of being able to find the last business day of the month in Excel cannot be overstated. It is an invaluable tool for businesses, investors, and anyone who needs to keep track of their finances. Whether you are trying to plan for the end of a month or you need to find out when a payment needs to be made, this formula is the perfect way to make sure you are always on top of things. With this formula, you can quickly find the last business day of the month in Excel with just a few simple steps.
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.