HomeGoogle DocsSpreadsheetHow to Return Nth Occurrence of a Day in a Month in...

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

Published on

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:

Day of the WeekWeekday No.
Sunday1
Monday2
Tuesday3
Wednesday4
Thursday5
Friday6
Saturday7

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.

Example: Finding Nth Occurrence of a Day in a Month in Google Sheets

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
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

11 COMMENTS

  1. I have a solution that is a bit more concise:

    In A1, put the earliest date of the month your occurrence could be. The day will be 1 for 1st, 8 for 2nd, 15 for 3rd, and 22 for 4th. Then, use this formula to add 0-6 days to that date using the WEEKDAY function:

    =A1+MOD(5-WEEKDAY(A1),7)

    5 above is the weekday number you want, Thursday in the above example. So the 2nd Thursday of April 2022 is 4/15/2022 (Friday) plus days equal to MOD(5-6,7). Which is MOD(-1,7), which is 6. 4/15/2022 + 6 = 4/21/2022, the 2nd Thursday in April 2022.

  2. When I enter 3/1/2020 in A2, 2 in B2, 1 in B3, and your dynamic formula in D2, the formula returns 10/22/1779. What’s wrong?

    • Hi, Bob Bethune,

      Using the same input values the formula returns me 03/08/2020.

      Happy to help if you can share a sample sheet. You can share the link (URL) of your sheet in your reply. I won’t publish the link.

  3. Thanks so much for this!

    If you’d like to take a step further and make it so you can create inputs for the day of the week and the nth week of the month, then you can do something like this….. E2 is the cell where you can put 1-7 for the day of the week (Sun=1, Mon=2, etc.)

    • Hi, James William Abraham,

      After reading your comment, I realized that I should bring some more dynamism to the formula. So I updated this post today.

      You can find the updated content at the bottom of this tutorial.

      Formula Logic:

      Generate a whole month’s date (using Sequence).
      Filter the generated dates based on Weekday (using Filter).
      Offset based on occurrence (using Index).

      When I wrote this tutorial back in 2017, the Sequence function was not available.

  4. Ok, I’m trying to find the next third Friday based on the current date. Let say today was 9/1/18 then the third next Friday would be 9/21/18. The formula works! Then at 9/22, the formula shows blank because of the if function has "" in it.

    If the day is after the current months third Friday and I want it to show the next 3rd Friday, what should be put in place of the ""?

    • Hi, John A Stefani,

      The basic of the formula lies in the date in A2. I have clearly mentioned in the tutorial it should be the first date of the month.

      That means if you want to find the 3rd occurrence of Monday or any other day in September 2018, you should enter the date 1-Sep-2018 in A2.

  5. This is a little hard to follow i admit it starts out amazing and i love how it explains things but then i find its hard to follow exactly what cell you are talking about.

    for instance, what goes in cell D3????

    • Hi, David,

      Sorry for the long delay in reply. I approved your comment and later forgot to attend it.
      Actually, there is nothing in D3. It was a typo error from my side. I am so sorry about that. It should be read as B2. I just changed that in my tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here