When it comes to preparing financial statements, finding the last Saturday of a specific month and year in Google Sheets can be quite useful. This is especially relevant for companies that end their fiscal year on the last Saturday of a month every year.
However, Google Sheets does not have a specific function to meet this requirement. But fear not, because I have a formula that utilizes the WEEKDAY and EOMONTH date functions to solve this problem.
Pre-Requisites – Input Date Values and Formatting
Let’s assume you want to find the last Saturday of September 2021 in Google Sheets. You can input the values for the formula in one of two ways:
- Enter the text “September” in cell A2 and 2021 in cell B2.
- Input a date that falls within September 2021 in cell C2 (you can format it to just show the month using Format > Number > Custom number format).
For option 1, you will additionally need to use the following formula in cell C2:
=DATE(B2,MONTH(A2&1),1)
This formula converts the month name in text to the month number.
In my case, I’m following option 2. So, in cell C2, I will enter any date that falls within September 2021, for example, 01/09/2021.
My formula in cell D2 will then return the date 25/09/2021, which is the last Saturday of that given month and year.
Formula to Get the Last Saturday of Any Given Month in Google Sheets
Assuming you have already entered the input values as described above, you can insert the following combination formula in cell D2:
=EOMONTH(C2,0)-(WEEKDAY(EOMONTH(C2,0))<>7)*WEEKDAY(EOMONTH(C2,0))
And voila! If you input 01/01/2022 in cell C2, you will get 29/01/2022 in cell D2.
You can also use an array formula to get the last Saturday in the subsequent years. For example, if the end month of the fiscal year is September, the company’s year-end could fall on any date from September 24 to September 30 in the years 2021-2025.
To achieve this, enter 01/09/2021, 01/09/2022, 01/09/2023, 01/09/2024, and 01/09/2025 in cell range C2:C6. Then, insert the following array formula in cell D2:
=ArrayFormula(EOMONTH(C2:C6,0)-(WEEKDAY(EOMONTH(C2:C6,0))<>7)*WEEKDAY(EOMONTH(C2:C6,0)))
To get the last Saturday in all the months of a given year in Google Sheets, use the following dates in the array C2:C13:
=ArrayFormula(EOMONTH(C2:C13,0)-(WEEKDAY(EOMONTH(C2:C13,0))<>7)*WEEKDAY(EOMONTH(C2:C13,0)))
The formula remains the same, only the cell range/array changes.
Formula Explanation
The formula that returns the last Saturday of any given month in Google Sheets consists of two parts:
Generic Formula: part_1 – part_2_a * part_2_b
-
Part_1:
EOMONTH(C2,0)- It converts the given date in cell C2 to the end of the month date.
-
Part_2:
(WEEKDAY(EOMONTH(C2,0))<>7) * WEEKDAY(EOMONTH(C2,0))- Let’s break it down into part_2_a and part_2_b.
-
Part_2_a:
(WEEKDAY(EOMONTH(C2,0))<>7)- This part returns TRUE (1) if the weekday of the end of the month of the date in cell C2 is not equal to 7 (Saturday), otherwise it returns FALSE (0).
-
Part_2_b:
WEEKDAY(EOMONTH(C2,0))- This part returns the weekday of the month of the date in C2.
In essence, part_2 will be:
- If the weekday of the end of the month in cell C2 is not Saturday, then it’s 1 * weekday_of_eomonth_of_C2.
- If the weekday of the end of the month in cell C2 is Saturday, then it’s 0 * weekday_of_eomonth_of_C2.
By subtracting this output from part_1, we get the last Saturday of the given month.
And that’s all you need to know about finding the final or last Saturday of any given month and year in Google Sheets. Enjoy!
Check out Crawlan.com for more SEO tips and tricks!
