Function Problems

How to Find the First Monday of Any Month Using Excel Formulas

Huma Ashraf Ch Oct 12, 2026 870 views

Question details

The user wants an Excel formula to automatically return the date of the first occurrence of a specific weekday, such as the first Monday, for any selected month.

How to Find the First Monday of Any Month Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Calculating specific scheduling or reporting dates dynamically based on inputted year and month values.
Observed behavior: A dynamic formula is needed to calculate the exact calendar date of a specific first weekday to replace manual calendar checking.

Before you start

Ensure you know the exact year and month you want to query, and verify that your target cell is formatted as a Date rather than General text or a number.

Solution 1Recommended

Use a Combination of DATE, MOD, and WEEKDAY Functions

This is the most robust method for calculating the exact date of a specific weekday using dedicated year, month, and day parameters.

This formula uses the logic that Monday = 1 through Sunday = 7. It calculates the first day of the month and uses the MOD function to offset the correct number of days to reach your desired first weekday.

Select the target cell

Click on the cell where you want the calculated date to be displayed.

Enter the formula

Type the formula: =DATE(year,month,1+MOD(desired_day-WEEKDAY(DATE(year,month,1),2),7)). To find the first Monday of August 2023, input: =DATE(2023,8,1+MOD(1-WEEKDAY(DATE(2023,8,1),2),7)).

Press Enter and Format

Press Enter on your keyboard. If the cell displays a 5-digit number, right-click the cell, select 'Format Cells', and choose a 'Date' format to view it properly.

Use a Combination of DATE, MOD, and WEEKDAY Functions

Modify for other weekdays: You can change the '1' in the MOD portion of the formula to another number (1 to 7) to find other days, such as 5 for the first Friday.

Solution 2

Calculate the First Monday Using an Existing Date Reference

If you already have a date value in your spreadsheet, you can use a shorter formula referencing that specific cell to find the first Monday of its respective month.

Powerful Spreadsheet Tool

Calculate Complex Dates Easily with WPS Spreadsheet

WPS Office offers full support for advanced spreadsheet functions, allowing you to use DATE, WEEKDAY, and MOD formulas seamlessly. It is an excellent, lightweight solution for your scheduling and data analysis needs.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
2. Enter your formula: Select an empty cell and paste the DATE and WEEKDAY combined formula.
3. Get instant results: Press Enter to instantly accurately calculate your desired calendar dates.

100% compatible with Microsoft Excel formulas and functionsFree and lightweight spreadsheet softwareRobust support for advanced date and time formattingUser-friendly interface for effortless data management

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the formula to find the first Friday instead of Monday?

In the formula =DATE(year,month,1+MOD(desired_day-WEEKDAY(...),7)), change the desired_day value. Since Monday is 1, Friday would be 5. Simply replace the '1' before the minus sign with a '5'.

Why does my formula return a random 5-digit number instead of a date?

Excel and WPS Spreadsheet store dates as sequential serial numbers. A 5-digit number means the calculation is correct, but the cell is formatted as 'General'. Simply right-click the cell, select 'Format Cells', and choose 'Date'.

Can I use cell references for the year and month in the formula?

Yes. Instead of typing fixed numbers like 2023 and 8, you can point to cells containing those values. For example, if B1 holds the year and B2 holds the month, use =DATE(B1,B2,1+MOD(1-WEEKDAY(DATE(B1,B2,1),2),7)).

Does this formula work in Google Sheets and WPS Office?

Yes, the DATE, MOD, and WEEKDAY functions are standard spreadsheet functions that work perfectly across Microsoft Excel, WPS Spreadsheet, and Google Sheets.

Excel & Data Problems