This guide will explain how to get the last Saturday of any given month and year in Google Sheets.
We’ll use a formula that determines the last day of the month and subtracts a certain number of days to find the last Saturday of the month.
Let’s begin with a quick use case for this formula.
Suppose you want to set up a monthly meeting for your team or organization. You’ve all decided as a group to meet on the last Saturday of every month.
As the team leader, you want to know whether any of these future meetings coincide with a holiday or other important function. How can you determine the date of the last Saturday of each month for the rest of the year?
Google Sheets includes several date functions that are helpful with these types of questions. For this use case, we will need two functions: EOMONTH and WEEKDAY.
In the next section, we’ll show how we can use these two functions together to find the last Saturday of any given month and year.
A Real Example of a Spreadsheet that Returns the Last Saturday of a Given Month and Year
Let’s look at a real example of a spreadsheet that uses Google Sheets formulas to determine the last Saturday in a given month.
In the example below, we used a formula to find the last Saturday of the month given the first day of the month.
To get the values in Column D, we just need to use the following formula:
=EOMONTH(C2,0)-(WEEKDAY(EOMONTH(C2,0))<>7)*WEEKDAY(EOMONTH(C2,0))
Let’s explore how this formula works. First, we used the EOMONTH function to get the end of the month given in column C. For example, if we add January 1st, 2022 as an argument to EOMONTH, it will return the date January 31st, 2022.
Now that we have the last day of the month, we must determine the day of the week of that date. Google Sheets provides a WEEKDAY function that returns a value between 1 and 7, depending on the day of the week. A value of 1 indicates a Sunday, while a value of 7 indicates a Saturday.
If the last value falls on a Saturday, then we do not need to adjust the value returned by EOMONTH. If it falls on any other day, we must go back to n days, where n equals the value returned by WEEKDAY(EOMONTH(C2,0)). For example, January 31st, 2022, is a Monday, which is also equivalent to 2 when using EOMONTH. This means we must go back two days to get the Saturday that falls on January 29th.
In the formula, we replicate this logic by multiplying the WEEKDAY result with (WEEKDAY(EOMONTH(C2,0))<>7) before subtracting from the EOMONTH result. If the weekday is a Saturday, then we’ll no longer subtract since (WEEKDAY(EOMONTH(C2,0))<>7) evaluates to FALSE or 0. Else, the conditional statement will return 1, and we’ll proceed to subtract n from our EOMONTH result.
We can change the formula slightly to work with different days of the week.
To find the last Sunday of a given month, we can use the following formula:
=EOMONTH(C2,0)-(WEEKDAY(EOMONTH(C2,0))<>1)*WEEKDAY(EOMONTH(C2,0)-1)
There are two main changes made to this formula. First, our conditional statement checks if the last day of the month is already a Sunday. Next, we subtract 1 from the result of WEEKDAY since we want to land one day ahead of Saturday.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out this formula in Google Sheets, head over to the next section to learn how.
How to Get Last Saturday of Any Given Month and Year in Google Sheets
This section will guide you through every step you need to determine the last Saturday of any given month and year in Google Sheets. In this example, we’ll create a table showing the last Saturday of each month in 2022.
Follow these steps to start finding the last Saturday of a given month:
- First, create the initial table of months in 2022. These cells will determine the day of the week of key dates in the month.
- Next, we’ll create a new column that will determine the first day of each given month. We’ll use the DATE function to construct this date. The first two arguments of this function are for the year and month. We’ll simply add ‘1’ as the third argument to specify the first day of that given month.
- Use the Fill Handle tool to determine the first day of each month in 2022.
- Next, we’ll add in our formula to get the last Saturday given the first day of that month.
- Use the Fill Handle tool once more to get the last Saturday for each month in the table.
- We can make small adjustments to this formula to work with other days of the week. In the example below, we’ve used a similar formula to find the last Sunday of each month in 2022.
That’s all you need to remember to start using the EOMONTH and WEEKDAY functions to get the last Saturday of any given month and year in Google Sheets. We’ve also shown how to adjust the formula to work for any day of the week.
You can now use this formula in Google Sheets together with the various other Google Sheets formulas available to create great worksheets that work for you.
Are you interested in learning more about what Google Sheets can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
