Table of Contents
Are you looking for an easy way to find the first business day of the month in Excel? It can be difficult to keep track of the days of the month and determine which are business days and which are not. In this guide, you will learn how to quickly and easily find the first business day of the month in Excel. We’ll walk you through the steps to create a dynamic formula that automatically adjusts which day is the first business day of the month. Let’s get started!
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.