You can use the following formula to find the first business day of the month for a given date in Excel:
=WORKDAY(EOMONTH(A2,-1), 1)
This formula will return the first business day of the month for the date in cell A2.
This formula works by using the EOMONTH function to find the last day of the month prior to the date in cell A2, then using the WORKDAY function to find the first weekday in the following month.
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 dataset into Excel:
Step 2: Calculate First Business Day of Month
Next, we will type the following formula into cell C2 to find the first business day of the month for the date in cell A2:
=WORKDAY(EOMONTH(A2,-1), 1)
We can then click and drag this formula down to each remaining cell in column C:
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 C2:C11 and then click the Number Format dropdown menu on the Home tab and then click Short Date:
Each of the numeric values in column C will be converted to a date format:
Column C shows the first 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 first business day of the month for January is on Monday the 3rd.
Thus, our formula returns 1/3/2022.
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
