You can use the following formula in Excel to find the first Monday of a given month:
=DATE(YEAR(A2),MONTH(A2),7)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),7),3)
This particular example returns the first Monday of the month for the date in cell A2.
The following example shows how to use this formula in practice.
Example: Find First Monday of Month in Excel
Suppose we have the following list of dates in Excel:
We can type the following formula into cell B2 to find the first Monday of the month for the date in cell A2:
=DATE(YEAR(A2),MONTH(A2),7)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),7),3)
We can then click and drag this formula down to each remaining cell in column B
Column B now contains the date of the first Monday of each month in column A.
For example, the first date of 1/1/2023 contains the month of January for 2023.
We can confirm that 1/2/2023 is indeed the first Monday of this month by referring to a calendar:
Similarly, the second date of 2/1/2023 contains the month of February for 2023.
We can confirm that 2/6/2023 is indeed the first Monday of this month by referring to a calendar:
How This Formula Works
Recall the formula that we used to find the first Monday of the date 1/1/2023:
=DATE(YEAR(A2),MONTH(A2),7)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),7),3)
Here is how this formula works:
First, we use the DATE, MONTH and YEAR functions to extract the seventh day of the month and year in cell A2. This returns 1/7/2023.
Next, we use the WEEKDAY function with the optional argument of 3 to specify that we would like to extract the number of the day of the week of 1/7/2023, assuming that the days are number from 0 (Monday) to 6 (Sunday).
This returns 5 since 1/7/2023 is on a Saturday.
Lastly, we use a subtraction sign to subtract 5 from 1/7/2023 to get 1/2/2023.
We use the same logic to find the first Monday of each date.
Note: You can find the complete documentation for the WEEKDAY function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate Average by Month in Excel
How to Calculate the Average by Group in Excel
How to Calculate 7-Day Moving Average in Excel
