How can I use Google Sheets to locate the first Monday of each month?

By /

Google Sheets is a powerful tool that can be used to locate the first Monday of each month. By utilizing the built-in formulas and functions, users can easily create a formula that will automatically identify and highlight the first Monday of every month. This can be helpful for various tasks such as scheduling, budgeting, or planning events. With the ability to customize the formula to fit specific needs, Google Sheets provides a convenient and efficient solution for locating the first Monday of each month.

Google Sheets: Find the First Monday of Each Month

You can use the following formula in Google Sheets 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 Google Sheets

Suppose we have the following list of dates in Google Sheets:

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:

Google Sheets find first Monday of the month

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 DATE(YEAR(A2),MONTH(A2),7) 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 process to find the first Monday of each date in column A.

Note: You can find the complete documentation for the WEEKDAY function in Google Sheets .

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

x

Related terms:

  1. Excel: Find the First Monday of Each Month
  2. How can I use Excel to locate the First Sunday of Every Month?
  3. How can I use Google Sheets to find the last business day of the month?
  4. How can I use the IF function in Google Sheets to make a decision based on the current month?
  5. How do I show the month name in Google Sheets?
  6. How to convert date to month and year format in Google Sheets?
  7. How to use Query in Google Sheets using a month number?
  8. How to Group Data by Month in Google Sheets (With Example)
  9. How can I find the last business day of the month on Google Sheets?
  10. How can I filter dates by month in Google Sheets?