How to calculate dates for things like 1st friday or 3rd saturday?


I took Dave Peterson's answer from 2005(!) and adapted it a bit so you can drag down in google sheets.

Example for 3rd saturdays:

In A1, put

In A2, put

enter image description here

That will calculate the year based on your current date. The part that is controlling the week number (ie 3rd or 1st) is that 7*3(3rd week) part. That part that is controlling the day of the week is the 8-7 part. Still trying to grok that. The +1 is adding a month, thus allowing you to do a fill down.

Test this against google calendar to get it right. Hope this can help someone stuck!

Make a copy of it if you'd like: public spreadsheet

| improve this answer | |

This post is a couple of years old as I'm responding. But in case anyone else is looking for a solution (and, very importantly, if you can live without a full explanation of why it works, which would be a bear to verbalize), here are some nerdy creations of mine:

Example 1


Drop-down data validation in A1 contains this List: 1st, 2nd, 3rd, 4th (Note: 5th can not be included, since not every month will contain a 5th Tuesday, etc., and including it would unnecessarily lengthen an already lengthy formula).

Drop-down data validation in A2 contains this List: SUN, MON, TUE, WED, THU, FRI, SAT.

A3 contains a start date.

A4 contains a stop date (i.e., one later than the date in A3).

Local settings are such that Sunday is counted as day 1 of each week.

The following array formula, placed anywhere on the sheet, will reference the above data in A1:A4 and create a list containing all matches (e.g., all 2nd Tuesdays, all 1st Thursdays, etc.) that fall between the start and end dates given in A3 and A4:

=ArrayFormula(QUERY(IF(ROW(INDIRECT("B1:B"&INT((A$4-A$3)/30)+1)),DATE(YEAR(A$3),(MONTH(A$3))+(ROW(INDIRECT("B1:B"&INT((A$4-A$3)/30)+1)))-1,1)+(VLOOKUP(A$2,{{"SUN";"MON";"TUE";"WED";"THU";"FRI";"SAT"},{1;2;3;4;5;6;7}},2,FALSE)-WEEKDAY(DATE(YEAR(A$3),(MONTH(A$3))+(ROW(INDIRECT("B1:B"&INT((A$4-A$3)/30)+1)))-1,1)))+((VALUE(LEFT(A$1,1))-IF((VLOOKUP(A$2,{{"SUN";"MON";"TUE";"WED";"THU";"FRI";"SAT"},{1;2;3;4;5;6;7}},2,FALSE)-WEEKDAY(DATE(YEAR(A$3),(MONTH(A$3))+(ROW(INDIRECT("B1:B"&INT((A$4-A$3)/30)+1)))-1,1)))>=0,1,0))*7),""),"Select * Where Col1 <="&A$4))

As with all array formulas, just make sure there is no existing data below the cell in which the array formula is placed, or it will error, because expanding the array would otherwise overwrite the existing data below.

Now, let's say you wanted to work in reverse. That is, you have a list of dates, and you need to know where they fell in their month (e.g., 1st Thursday, 3rd Tuesday, etc.).

Example 2


A list of valid dates is placed in Column A.

The following array formula, placed anywhere else on the sheet, will provide the ordinal day of the week for each date in the Column-A list:

=ArrayFormula(ROUNDUP(DAY(QUERY({A:A},"Select * Where Not Col1 = '' Limit "&COUNT(A:A)))/7,0)&CHOOSE(ROUNDUP(DAY(QUERY({A:A},"Select * Where Not Col1 = '' Limit "&COUNT(A:A)))/7,0),"st ","nd ","rd ","th ","th ")&CHOOSE(WEEKDAY(QUERY({A:A},"Select * Where Not Col1 = '' Limit "&COUNT(A:A))),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"))

Of course, the range A:A can be changed to suit the actual range where the list of dates resides.

Again, these formulas are designed to be helpful and perhaps to invite the curious to untangle them. They will not be terribly helpful as tutorials.

| improve this answer | |
  • Would you be able to put this into a sample sheet like the OP did? I would really appreciate it. – grbonk Dec 18 '18 at 0:29
  • @grbonk, do you have an actual use for this? If so, my post lists step-by-step instructions for creating the sheet and getting the results. Posting my own sample sheet would mean keeping it on my drive linked here, which I don't want to do. However, if you create the sheet according to my directions and run into any problems, feel free to post your link and I'll work with what you have there to clarify or adapt. – Erik Tyler Dec 19 '18 at 0:59
  • you are absolutely correct, Once I re-read the steps a few times I was able to get the examples to work. docs.google.com/spreadsheets/d/… – grbonk Jan 11 '19 at 15:00
  • Happy to hear it, @grbonk. – Erik Tyler Jan 12 '19 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.