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

Your Answer

 
discard

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.