Calculate Nth Occurrence of a Day in a Month in Google Sheets

This guide will explain how to use a custom formula in Google Sheets to calculate the nth occurrence of a specific weekday in any given month.

When working with dates and schedules in your spreadsheet, you may need to find specific recurring dates such as “the 3rd Sunday of May” or “the 2nd Tuesday of each month.” These calculations are common in payroll systems, meeting schedules, holiday calculations, and event planning.

While Google Sheets does not have a built-in function to directly calculate nth weekday occurrences, we can achieve this using a custom formula that combines several date functions to pinpoint exactly the date we need.

In this guide, we will provide a step-by-step tutorial on how to use this nth weekday occurrence formula in Google Sheets to calculate recurring dates with precision.

The Anatomy of the WEEKDAY Function

The syntax of the WEEKDAY function is as follows:

=WEEKDAY(date, [type])

Let’s look at each argument to understand how to use the WEEKDAY function.

• WEEKDAY() refers to the function of the same name. This function returns a number representing the day of the week for a specified date.
• date argument refers to the date value for which you want to determine the day of the week. This can be a cell reference containing a date, a date entered directly, or a formula that returns a date.
• type is an optional argument that determines which numbering system to use for the days of the week. By default, this value is set to 1.
• If type is set to 1, Sunday is considered day 1 and Saturday is day 7. When set to 2, Monday becomes day 1 and Sunday becomes day 7. When set to 3, Monday is day 0 and Sunday is day 6.
• If date is not a valid date or cannot be interpreted as a date, the WEEKDAY function returns an error.

A Real Example of Returning the Nth Occurrence of a Day in a Month in Google Sheets

Let’s explore a few simple examples where we can use a custom formula to return the nth occurrence of a specific day of the week in a given month.

Suppose we pay a utility bill every 3rd Friday of the month. We want to identify what specific date we’ll need to pay our bills for the next six months.

In the sheet above, we set up a table to calculate the third Friday of each month from January to June for 2026.

To retrieve this conditional ranking, we can use the following formula:

=A2-DAY(A2)+1+C2*7-WEEKDAY(A2-DAY(A2)+8-B2)

This formula calculates the date of the nth occurrence of a specific weekday in a given month. 

Let’s break it down step by step:

1. A2-DAY(A2)+1 – This gets the first day of the month
   • DAY(A2) extracts the day number from the date
   • Subtracting it and adding 1 gives you the 1st of that month
2. C2*7 – This adds the appropriate number of weeks
   • For the 3rd occurrence, this adds 21 days (3×7)
3. WEEKDAY(A2-DAY(A2)+8-B2) – This tricky part calculates an adjustment
   • A2-DAY(A2)+8-B2 creates a reference date
   • The +8 is key – it ensures we’re looking at the right week context
   • We then use WEEKDAY to get how many days to adjust
4. The final subtraction removes the excess days to land exactly on the target weekday

Click on the link below to create your copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the function in Google Sheets.

How to Return Nth Occurrence of a Day in a Month in Google Sheets

1. First, let’s create a table where the user will input the necessary data to calculate the nth occurrence of a day in a month.
   
   Our calculation needs the following: the month to use, the day of the week, and which occurrence to calculate for.
   
   
2. Next, we’ll write our custom formula in cell D2 to calculate the nth occurrence of a day in a month.
   
   In our example above, we used the following formula: =A2-DAY(A2)+1+C2*7-WEEKDAY(A2-DAY(A2)+8-B2)
   Note that you’ll need to adjust this formula’s cell references depending on how your dataset is structured. 
3. Hit the Enter key to evaluate the formula.
   
   The formula states that the first Sunday of July 2025 is on July 6th.
4. Modify the input to find the nth occurrence of any day of the week in a specified month.
   
   For example, adding different input allows us to find out that the third Friday of January 2026 falls on January 16th.

To learn more about calculating dates in Google Sheets, you can read our post on how to extract the time from a datetime value in Google Sheets.

That’s all for this guide! Don’t forget to check out our library of spreadsheet resources, tips, and tricks for both Google Sheets and Microsoft Excel!