If you’re creating a timetable for students in Google Sheets, or just planning ahead, you may need to know when is the last Monday of the month (or any other last weekday day of the month).
In this tutorial, I will show you how to use formulas in Google Sheets to do this.
Formula to Calculate the Last Monday of the Month
Suppose you want to know what would be the date of the last Monday in the month of June 2018.
Below is the formula that will give it to you:
How does the formula work?
There are 3 Google Sheets functions that are used to calculate this:
- EOMONTH: This function finds the last date of a given month. It stands for ‘End Of Month’.
- WEEKDAY: This function tells us the weekday number of a given date. In this example, the last date of June 2018 was 30/6/2018 which was a Saturday. So Weekday function gave us 6 for Saturday (the numbering started from Monday. So Monday is 1, Tuesday is 2, and so on).
- DATE: This function gives us the date when we specify the year, month and day value.
Now let me try and break it down further:
This part of the formula would give us the last date in June 2018. Note that I have used ‘0’ as the second argument (which gives us the last date of the month to which the date in the first argument belongs).
You can also use this function to get the last date of the previous/next month (instead of 0 use 1 for next month and -1 for the previous month).
The above part of the formula tells us what weekday is the last day of the month. In this example, this will return 6, as the last day of the week is Saturday.
Finally, this formula gives us the last Monday of the month.
You can also use this same technique to calculate any day of the month. For example, if you want to know the third Thursday in June 2018, you can use the below formula:
This technique can also be used to calculate holidays in a year. For example, if you want to know the date of Labor Day, which is first Monday in September, then you can use this technique.
You May Also Like the Following Google Sheets Tutorials:
- Calculate Age in Google Sheets (using the Date of Birth).
- Calendar Template in Google Sheets (Monthly and Yearly).
- Calculate the Number of Days Between Two Dates in Google Sheets.
- Leave Tracker Template in Google Sheets.
- How to Insert Timestamp in Google Sheets.