When preparing financial reports or planning schedules, you might need to find the last Saturday of a given month and year in Google Sheets. This is especially useful if your company closes its monthly books on the last Saturday.
Google Sheets doesn’t have a built-in function for this, but with a simple combination of functions like EOMONTH and WEEKDAY, you can create a dynamic formula to get the result.
Sample Data and Input Format
You can input the month and year in one of the following two ways:
Option 1:
- Enter the text “September” in cell A2.
- Enter the year, e.g. 2021, in cell B2.
- Use this formula in C2 to convert it to a proper date:
=DATE(B2, MONTH(A2&1), 1)
Option 2 (Recommended):
- Enter any date from the target month in cell C2 (e.g.,
01/09/2021for September 2021).
We’ll use Option 2 in our main formula example.
Formula to Get the Last Saturday of Any Given Month
Enter your input date in cell C2 (e.g., 01/09/2021). Then, in cell D2, use the following formula:
=EOMONTH(C2, 0) - (WEEKDAY(EOMONTH(C2, 0)) <> 7) * WEEKDAY(EOMONTH(C2, 0))This returns the last Saturday of the month of the date entered in C2. For example, if you enter 01/01/2022, it will return 29/01/2022.
Find the Last Saturday for Multiple Months
If you want to find the last Saturday for several months, list the dates in range C2:C like:
- 01/09/2021
- 01/09/2022
- 01/09/2023
- 01/09/2024
- 01/09/2025
Then use this array formula in D2:
=ArrayFormula(TO_DATE(IF(C2:C="",,EOMONTH(C2:C, 0) - (WEEKDAY(EOMONTH(C2:C, 0)) <> 7) * WEEKDAY(EOMONTH(C2:C, 0)))))
Last Saturday in All Months of a Year
To find the last Saturday for each month in a single year:
Enter the following formula in any empty column:
=ArrayFormula(LET(dt, EDATE(EOMONTH(DATE(2022, 1, 1), -2)+1, SEQUENCE(12)), TO_DATE(IF(dt="",,EOMONTH(dt, 0) - (WEEKDAY(EOMONTH(dt, 0)) <> 7) * WEEKDAY(EOMONTH(dt, 0))))))Replace the year 2022 with the year you want.
Here we have used EDATE to generate all month start dates in the provided year.
Formula Explanation
Let’s break down the base formula:
=EOMONTH(C2, 0) - (WEEKDAY(EOMONTH(C2, 0)) <> 7) * WEEKDAY(EOMONTH(C2, 0))
EOMONTH(C2, 0): Gets the last day of the month for the date in C2.WEEKDAY(...): Returns the weekday number (1=Sunday, 7=Saturday).(WEEKDAY(...) <> 7): Checks if the end-of-month is not Saturday (TRUE = 1, FALSE = 0).- If it’s not Saturday, subtract the weekday number from the date to get the last Saturday.
Use this technique whenever you need to calculate the final Saturday of a fiscal period, schedule events, or automate reporting in Google Sheets.
Got questions? Leave a comment!
