I think it’s wise to explain what I meant by saying return 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, you can use the below formula. For example the date of first or second Monday in a month, third Tuesday in a month or so on.
I hope you understood the above concept. We are using Google Sheets following functions in the formula. Either you can directly use the formula for your use or understand the formula first by reading the below tutorials about the functions used in the formula.
Google Sheet Functions Used to Return Nth Occurrence of a Day in a Month
In the above function 2 and 3, any of the tutorial will do your job.
Now back to our tutorial to find how to return nth occurrence of a day in a given month.
Steps Involved in the Google Sheets Formula to Return Nth Occurrence of a Day in a Month
In any cell (here in cell A2), enter the date 2/1/2017 that is on “mm/dd/yyyy” format. You can opt your default date format in your Google Spreadsheet.
Why this date?
In this example I am going to find the date of 3rd Tuesday in February 2017. If you want to find the nth day occurrence of Monday in December 2020, you should enter the date as “12/1/2020”. That means we should enter the first date of the month in question.
In another cell, here B2, enter number 3. As we are going to find the date of third Tuesday in February.
Below is the formula.
If you copy and paste the formula, rewrite the quotes to avoid possible formula error.
I will explain the formula. Using the WEEKDAY function in the formula, first we fill find the weekday number of the date in Cell A2. The below are the 7 weekday numbers.
- Sunday – 1
- Monday – 2
- Tuesday – 3
- Wednesday – 4
- Thursday – 5
- Friday – 6
- Saturday – 7
When you check the first part of the formula you can see as below;
It tells the formula what to do if weekday number is 1. If weekday number is 1, that is Sunday, the formula will add 2 to the date in Cell A2 to make it Tuesday.
In our formula Weekday number is 4. Because February 1 is Wednesday and the identification number is 4. So the formula execute the below part in the formula.
It will add 6 days to the date in Cell A2 and the result will be “2/7/2017”. This is the first occurrence of the Tuesday in February 2017. We need third occurrence. For that use the below formula in another cell.
This formula will return the date 2/21/2017 that is the third occurrence of Tuesday in February.
This formula will do one more thing. If you enter any invalid occurrence number in Cell D3, it shows the result as blank cell. You can combine the above both formula, but it will make the formula lengthy.
Now when you want to return nth occurrence of Monday in a month, just change the first formula like this. Only the first part of the formula, I am explaining below.
You should change the “A2+” part only as per the date numbers given above. If A2 is Sunday, then add 1 day to it. Accordingly adjust the rest part of the formula. Actually the whole thing is simple.
Get more from our eBooks and Paperbacks.