When working with dates in Excel, there might be situations where you need to determine the first Monday of a given month. Excel provides various functions that allow us to calculate this dynamically. In this article, we will explore different formulas to find the first Monday of a month and apply them to the months of 2025.
Nội dung
1. Finding the First Monday of Any Month
You can use one of the following formulas to find the first Monday of any month based on a given date in cell A2
.
Method 1: Using the 8th Day of the Month
=DATE(YEAR(A2), MONTH(A2), 8) - WEEKDAY(DATE(YEAR(A2), MONTH(A2), 6))
Explanation:
DATE(YEAR(A2), MONTH(A2), 8)
: Generates the 8th day of the month, which ensures that the first Monday has already passed.WEEKDAY(DATE(YEAR(A2), MONTH(A2), 6))
: Determines the weekday number of the 6th day of the month.- Subtracting the
WEEKDAY
value from the 8th day gives us the first Monday.
Method 2: Using the 7th Day of the Month
=DATE(YEAR(A2), MONTH(A2), 7) - WEEKDAY(DATE(YEAR(A2), MONTH(A2), 7), 3)
Explanation:
DATE(YEAR(A2), MONTH(A2), 7)
: Generates the 7th day of the month.WEEKDAY(DATE(YEAR(A2), MONTH(A2), 7), 3)
: Returns the weekday position of the 7th, where Monday = 0.- Subtracting this value from the 7th gives the first Monday of the month.
Example for 2025:
Month | First Day | First Monday |
January | 2025-01-01 | 2025-06-01 |
February | 2025-02-01 | 2025-03-02 |
March | 2025-01-03 | 2025-03-03 |
April | 2025-01-04 | 2025-07-04 |
May | 2025-01-05 | 2025-05-05 |
June | 2025-01-06 | 2025-02-06 |
July | 2025-01-07 | 2025-07-07 |
August | 2025-01-08 | 2025-04-08 |
September | 2025-01-09 | 2025-01-09 |
October | 2025-01-10 | 2025-06-10 |
November | 2025-01-11 | 2025-03-11 |
December | 2025-01-12 | 2025-01-12 |
2. Find the First Monday of the Current Month
If you want to find the first Monday of the current month dynamically, use:
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) + CHOOSE(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), 1, 0, 6, 5, 4, 3, 2)
Explanation:
TODAY()
: Returns the current date.YEAR(TODAY())
andMONTH(TODAY())
: Extracts the current year and month.- The rest of the formula works similarly to the first formula, adjusting the first day of the month to the nearest Monday.
This formula updates automatically every month based on the system date.
Conclusion
Using these formulas, you can quickly determine the first Monday of any month, whether it’s a fixed date or dynamically based on the current month. These techniques are helpful for setting up schedules, work plans, and reminders in Excel.
See more: