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
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-7))

In A2, put
=DATE(YEAR(A1),MONTH(A1)+1,1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,8-7))"

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

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

Given:

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

Given:

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.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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