Find the date of the first Monday of each month

In Excel, there is a very useful function called WEEKDAY, it can help users determine the weekday for a given date. This function is very simple to use, but if you want to find the first Monday of each month, Excel does not provide a direct function. So how do we solve this problem? Here we present a detailed tutorial on date functions to teach you how to use existing functions to easily calculate the first Monday of each month.

Solution

We can calculate the first Monday of each month with a formula. Below are the steps and the formula details:

• Use the DATE functionFirst, we need to generate the first day of each month. This can be done using the DATE function. For example,DATE(年, 月, 1) returns the first day of that year and month.

• Combine with the WEEKDAY functionNext, use the WEEKDAY function to determine which weekday that date falls on. For example,WEEKDAY(DATE(年, 月, 1), 12) returns a number indicating the weekday, where Tuesday = 1, Wednesday = 2, and so on, up to Monday = 7.

• Calculate the date of the first MondayTo find the first Monday of each month, we can use the following formula:
• This formula means subtracting the weekday number of that date from 8. The final result gives us the day of the month for the first Monday.

=8-WEEKDAY(DATE(B3,C3,1),12)

Detailed explanation of WEEKDAY

• As forWEEKDAY(DATE(B3,C3,1),12)it is to find the parameter for June 1, 2019.
• For example, June 1, 2019 is a Saturday, so the result is 5, because that '12' causes Tuesday to be represented as 1, Wednesday as 2, ... Monday as 7.
• The mapping for that parameter can be referenced in the table below:

Parameter	Return values
1 or omitted	Numbers 1 (Sunday) through 7 (Saturday). Same behavior as earlier versions of Microsoft Excel.
2	Numbers 1 (Monday) through 7 (Sunday).
3	Numbers 0 (Monday) through 6 (Saturday).
11	Numbers 1 (Monday) through 7 (Sunday).
12	Numbers 1 (Tuesday) through 7 (Monday).
13	Numbers 1 (Wednesday) through 7 (Tuesday).
14	Numbers 1 (Thursday) through 7 (Wednesday).
15	Numbers 1 (Friday) through 7 (Thursday).
16	Numbers 1 (Saturday) through 7 (Friday).
17	Numbers 1 (Sunday) to 7 (Saturday).

• Finally, subtracting from 8 gives the answer: 3, meaning June 3, 2019 is the first Monday of that month.

Mapping between the first weekday of each month and the parameter

Based on the above parameter mapping, we can deriveParameters for the first weekday of each month：

• Sunday: 11
• Monday: 12
• Tuesday: 13
• Wednesday: 14
• Thursday: 15
• Friday: 16
• Saturday: 17