Have you ever needed to find the date of a specific occurrence of a day in a month? Whether it’s finding the first Monday or the third Tuesday, I have a formula that can help you accomplish this in Google Sheets.
The Concept
Let me explain what I mean by “finding the Nth occurrence of a day in a month.” This refers to retrieving the date for a particular day (Monday, Tuesday, etc.) in any given month of any year. For example, you might want to know the date of the second Friday in a month or the fourth Wednesday. The possibilities are endless!
Google Sheets Functions Used
To achieve this, I’ve combined several Google Sheets functions to create a powerful formula. Here are the functions that make up this combo:
- Date Functions:
- WEEKDAY
- TO_DATE
- EOMONTH
- Logical Functions:
- IFS
- IF
You can use either the IFS or IF function, as both are capable of accomplishing the task at hand.
Step-by-Step Guide
Let’s dive into the steps involved in using the Google Sheets formula to find the Nth occurrence of a day in a month:
-
In any cell (let’s use cell A2), enter a date. For example, you can enter 2/1/2017 in the format that suits you best.
-
In another cell (let’s use B2), enter a number representing the occurrence you want to find. For instance, if you’re looking for the third Tuesday, enter 3.
-
Now, let’s write the formula. In cell C2, use the following formula to find the first Tuesday in February 2017:
=to_date(ifs(weekday(A2)=1,A2+2,weekday(A2)=2,A2+1,weekday(A2)=3,A2,weekday(A2)=4,A2+6,weekday(A2)=5,A2+5,weekday(A2)=6,A2+4,weekday(A2)=7,A2+3))- To find the Nth occurrence of the day, we need to use another formula. Enter the following formula in cell D2:
=if(((B2-1)*7+C2)<=eomonth(A2,0),((B2-1)*7+C2),"")This formula will return the desired date, such as 2/21/2017 for the third occurrence of Tuesday in February 2017.
- If you enter an invalid occurrence number in cell B2, the result will be blank. You can combine the above formulas, but I recommend using them separately for readability.
Changing the Weekday in the Formula
Do you need to find the Nth occurrence of a day other than Tuesday? No problem! Follow these steps:
- Let’s say you want to find the fourth Monday in December 2020. Modify the formula in cell C2 as follows:
=to_date(ifs(weekday(A2)=1,A2+1,weekday(A2)=2,A2,weekday(A2)=3,A2+6,weekday(A2)=4,A2+5,weekday(A2)=5,A2+4,weekday(A2)=6,A2+3,weekday(A2)=7,A2+2))Change the date in cell A2 to 12/1/2020 (mm/dd/yyyy) and B2 to 4 (representing the fourth occurrence). The formula in cell D2 will now return the date 12/28/2020.
And just like that, you’ve learned how to find the Nth occurrence of a day in a month using Google Sheets. Isn’t it amazing?
As a bonus, I want to offer you my free Yearly Calendar Template for any year from 2018 to the present. You can even highlight holidays from a list you provide. Feel free to check it out on Crawlan.com.
A More Dynamic Formula
If you prefer an even more flexible formula, I’ve got you covered. Follow these steps:
-
Enter the first date of any month in cell A2.
-
In cell B2, enter the occurrence number to represent 1st, 2nd, 3rd, and so on.
-
In cell B3, enter the weekday number. Refer to the “Weekday Numbers in Google Sheets” section above for the corresponding numbers.
For example, if you want to find the second occurrence of Sunday in October 2020, enter 10/1/2020 in cell A2, 2 in cell B2, and 1 (weekday number for Sunday) in cell B3. -
Now, use the following formula in any cell:
=to_date(index(filter(sequence(days(eomonth(A2,0),A2)+1,1,A2),weekday(sequence(days(eomonth(A2,0),A2)+1,1,A2))=B3),B2,1))This formula will return the date 10/11/2020 (mm/dd/yyyy) as the second occurrence of Sunday in October 2020.
With this dynamic formula, you can find the Nth occurrence of any day in any given month. It’s like magic!
Remember, whenever you need help with Google Sheets or any other online tools, you can always count on Crawlan.com for expert guidance.
Now go ahead and impress your friends with your newfound Google Sheets skills! Happy spreadsheet-ing!
