In this tutorial, you will learn how to get the first Monday of month in google sheets. Also, you will see how to get the last Monday as well.
To get the first Monday of month in google sheets, you can use the EOMONTH formula with the WEEKDAY formula.
Here is how to get the first Monday of month in google sheets:
1. Open google sheets on your computer or mobile
2. In cell A2, write today’s date
3. Highlight cell B2
4. Write formula
=IF(WEEKDAY(EOMONTH(A2,-1)+1)=2,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1)<2,1+EOMONTH(A2,-1)+1,EOMONTH(A2,-1)+1+(7-(WEEKDAY(EOMONTH(A2,-1)+1)-2))))
5. Press enter to get first Monday of month in google sheets
Here is an animation showing this in action:
Explanation of the Formula:
=IF(WEEKDAY(EOMONTH(A2,-1)+1)=2,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1)<2,1+EOMONTH(A2,-1)+1,EOMONTH(A2,-1)+1+(7-(WEEKDAY(EOMONTH(A2,-1)+1)-2))))Note: Weekday starts from Sunday which is 1 and ends on Saturday which is 7.
We are using the IF formula. First, we are checking if the first day of the month is a Monday. Here 2 is for Friday.
If the first date of the month is a Monday, then output that date.
Else, check if the first date of the month is less than Monday, if yes then add 1 to get the first Monday. Else if the first date of the month is greater than Monday then add (7-(current day -2)) to get the first Monday of month in google sheets.
Here,
- 7 is the count of total days in a week
- (Current day-2) will tell us how many days the current date is greater than last Monday
- (7-(current day -2)) will tell us how many days to add to the current date to get the next Monday.
Example – First Monday of month in google sheets
We have a list of dates in column A. We want to know the first Monday of the current month for each date.
For the first Monday of month in google sheets, use the below formula:
=IF(WEEKDAY(EOMONTH(A2,-1)+1)=2,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1)<2,1+EOMONTH(A2,-1)+1,EOMONTH(A2,-1)+1+(7-(WEEKDAY(EOMONTH(A2,-1)+1)-2))))
To get the first Monday of next month, you have to pass 1 in the EOMONTH formula and for the previous month, you will pass -1.
Here is the formula for the first Monday of next month in google sheets:
=IF(WEEKDAY(EOMONTH(A2,0)+1)=2,EOMONTH(A2,0)+1,IF(WEEKDAY(EOMONTH(A2,0)+1)<2,1+EOMONTH(A2,0)+1,EOMONTH(A2,0)+1+(7-(WEEKDAY(EOMONTH(A2,0)+1)-2))))Here is for the previous month:
=IF(WEEKDAY(EOMONTH(A2,-2)+1)=2,EOMONTH(A2,-2)+1,IF(WEEKDAY(EOMONTH(A2,-2)+1)<2,1+EOMONTH(A2,-2)+1,EOMONTH(A2,-2)+1+(7-(WEEKDAY(EOMONTH(A2,-2)+1)-2))))You can see both the formulas in action in the below sheet:
Last Monday of current month in google sheets
To get the last Monday of current month:
1. Open google sheets on your computer or mobile
2. In cell A2, write today’s date
3. Highlight cell B2
4. Write formula:
=IF(WEEKDAY(EOMONTH(A2,0))=2,EOMONTH(A2,0),IF(WEEKDAY(EOMONTH(A2,0))<2,EOMONTH(A2,0)-6,EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0)-2)))5. Press enter to get last monday of current month
Example – Last Monday of current month in google sheets
Below we have a list of dates in column A. For each of the dates, we want to find the last Monday of the current month, the previous month, and the next month.
The formulas are below:
Last Monday of the current month:
=IF(WEEKDAY(EOMONTH(A2,0))=2,EOMONTH(A2,0),IF(WEEKDAY(EOMONTH(A2,0))<2,EOMONTH(A2,0)-6,EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0)-2)))
Last Monday of last month:
=IF(WEEKDAY(EOMONTH(A2,-1))=2,EOMONTH(A2,-1),IF(WEEKDAY(EOMONTH(A2,-1))<2,EOMONTH(A2,-1)-6,EOMONTH(A2,-1)-WEEKDAY(EOMONTH(A2,-1)-2)))
Last Monday of next month:
=IF(WEEKDAY(EOMONTH(A2,1))=2,EOMONTH(A2,1),IF(WEEKDAY(EOMONTH(A2,1))<2,EOMONTH(A2,1)-6,EOMONTH(A2,1)-WEEKDAY(EOMONTH(A2,1)-2)))Here is a screenshot showing all the 3 formulas in action:
Wrapping up
In this tutorial, you learned how to get the first Monday of month in google sheets. Also, you learned how to get the last Monday of the month. You saw how we calculated the same for the previous month and the next month.
Now go ahead and use these formulas in your worksheet.
Appendix
[1] Double underline in accounting – Link
Further Reading
New to google sheets ? Start here
More related to Formulas:
Learn more about Google sheets Formulas.
Logical functions in google sheets
Error handling in google sheets
