I think it’s wise to explain what I meant by saying “Return the Nth occurrence of a day in a month.”

When you want to return the date of any particular occurrence of a day in any month of any year, you can use my formula.

For example, you can get the date of the first or second Monday in a month, the third Tuesday in a month, and so on. I hope you can understand the concept.

Prerequisites

In any cell (here, in cell A2), enter the date 01/03/2024, which is in DD/MM/YYYY format. However, you should use the default date format in your Google Spreadsheet.

Why is this date in cell A2? Can I use any other date for my testing?

In this example, I am going to find the date of the 3rd Tuesday in March 2024. If you want to find the nth occurrence of Monday in December 2024, you should enter the date as 01/12/2024.

That means you should enter the first date of the month in question, i.e., the beginning of the month date. Then you should tweak the formula a little bit as the default formula is set for Tuesday. I’ll come to that.

First, let’s begin with the 1st, 2nd, and 3rd Tuesday in a month in a specific year. Then I will tell you what to do with the days other than Tuesday.

In another cell, here B2, enter the number 3, i.e., the occurrence number, as we are going to find the date of the third Tuesday in March 2024.

Dynamic Formula to Return 1st, 2nd, 3rd, or 4th Occurrence of a Day in a Month

In cell C2, enter the following formula to return the third occurrence of Tuesday in March 2024:

=LET(
   dt, A2, n, B2, wn, 3, 
   ow, SEQUENCE(7, 1, dt), 
   fnl, TO_DATE(FILTER(ow, WEEKDAY(ow)=wn)+(n-1)*7), 
   IF(
      NOT(
            ISBETWEEN(fnl, EOMONTH(dt, -2)+1, EOMONTH(dt, 0))
      ), dt, fnl
   )
)

Why did I call it a dynamic formula? Before addressing that, let me explain how to find the nth occurrence of any specific day.

Please refer to the weekday numbers below:

To find the 3rd occurrence of Sunday in March 2024, in the formula, you should replace 3 (highlighted in yellow) with 1. For the third occurrence of Friday, replace 3 with 6. These are weekday numbers, and you can pick the one from the above table.

The date is in cell A2 and the occurrence number is in cell B2.

Now you can use this formula with any date, occurrence number, and day of the week.

Now, why is this formula dynamic?

If the entered occurrence number (here, in cell B2) does not fall in that month, the formula will return the date without any change.

If the entered occurrence number is 0 (zero), the formula will return the last occurrence of the specified day in the previous month. So, if you want to find the last occurrence of a specific date, you can use this formula.

For example, if you want to find the last occurrence of Sunday in June 2024, what you should do is enter 01/07/2024 in cell A2 and specify 0 in cell B2. Then, in the formula, replace the weekday number 3 (Tuesday) with 1 (Sunday). The formula will return the date 30/06/2024, which is the last Sunday in June.

Occurrence numbers -1, -2, -3, etc., will return the occurrences backward from 0.

Formula Explanation

I’ve used the LET function to simplify the formula. This function helps us define names with formula expressions.

Defined names and formula expressions:

• dt: represents cell reference A2 (date).
• n: represents cell reference B2 (nth occurrence).
• wn: represents 3 (Tuesday) or the weekday number you specified by replacing the yellow highlighted number.
• ow: SEQUENCE(7, 1, dt) which returns 7 dates starting from the date in cell A2.
• fnl: TO_DATE(FILTER(ow, WEEKDAY(ow)=wn)+(n-1)*7) filters ow (sequence of dates) if the weekday of ow is equal to wn (specified weekday number). With that, we added (n-1)*7 days. That will be the nth occurrence.
• IF(NOT(ISBETWEEN(fnl, EOMONTH(dt, -2)+1, EOMONTH(dt, 0))), dt, fnl): This is an IF logical test that evaluates fnl. If fnl is not between the current month’s last date or the previous month’s first date, the formula will return dt (A2); otherwise, it returns fnl.

That concludes the formula explanation of the dynamic formula that returns the nth occurrence of a specific day in a month in Google Sheets.

The Functions Used to Find the Nth Occurrence of a Day in a Month in Google Sheets

Here are the functions involved in the formula that finds the nth occurrence of a specified day in a month and their roles:

• LET: Used to simplify the formula.
• SEQUENCE: Returns 7 dates in a sequence starting from the date in cell A2.
• FILTER: Filters the sequence output matching the specified weekday number in the formula.
• TO_DATE: Converts date value to a formatted date.
• WEEKDAY: Returns weekday numbers of the sequence dates.
• ISBETWEEN: Tests whether the nth occurrence date is between the month-end date or the previous month’s first date and returns TRUE or FALSE.
• NOT: Converts TRUE to FALSE and FALSE to TRUE.
• IF: Logical test to return the date in cell A2 or the nth occurrence date based on occurrence date.

Resources

1. Find the First and Last Workdays of a Month in Google Sheets
2. Get Last Saturday of Any Given Month and Year in Google Sheets
3. Finding the First Sunday, Monday, etc., Date in a Month with Google Sheets