Date Patterns - Is there a formula for n-th Day of the Month Holidays?

Your examples are in the form of functions but there is no ability to create new functions and there are no functions that will do this. It is possible, though, to create a formula to do it in a cell.

When a formula requires several intermediate steps (like this one does), what looks simple when you use several cells to build up to the result becomes pretty complicated when it is all shoved into a single cell. You usually end up doing the same small calculations repeatedly in the one formula vs doing them one time in another cell and referencing their results repeatedly. It also makes it very hard to debug if something is not working right.

I did not try to parse "4th Thursday in November, 2021" into the numeric values required for the formula. That would be several steps all by itself and the resulting single-cell formula would have to do the same steps multiple times, making it horrendously more complicated than it already is. It could be done, though.

I cannot guarantee the formula I came up with works. I think it does but I didn't really test it much. I created the multi-cell version, the one that does each step individually (in cells B7-B11), tested it on a few dates, then I copied the formula from B11, pasted it into B13, and one-by-one, replaced the cell references to cells B10 with the formula in B10, then the references to B9 with the formula in B9, and so on until it only referenced the input values. In some cases this might require putting parentheses around the formulas but it wasn't necessary here.

"Day of week" is as defined by Numbers. Sunday =1, Monday = 2, etc.

Formulas are

B7 =DATE(B3,B2,1)

B8 =WEEKDAY(B7)

B9 =MOD(B4−B8+7,7)+1

B10 =B9+(B5−1)×7

B11 =IF(B10≤DAY(EOMONTH(B7,0)),DATE(B3,B2,B10),"does not exist")

B13 =IF(MOD(B4−B8+7,7)+1+(B5−1)×7≤DAY(EOMONTH(DATE(B3,B2,1),0)),DATE(B3,B2,MOD(B4−B8+7,7)+1+(B5−1)×7),"does not exist")

Hi Michael,

"Is there a formula that lets you determine the date of the day-of-week that is Thanksgiving on any given year?"

The easiest method for holidays in your second group is likely a lookup table.

U.S. Thanksgiving, celebrated on the fourth Thursday of November, always comes 21 days after the first Thursday in the month. The earliest possible date occurs when November 1 is a Thursday, making the fourth Thursday November 22. When November 1 is a Friday, the first Thursday does not occur until November 7, and U.S. Thanksgiving is celebrated November 28.

Dates for the four holidays in this group will follow the pattern shown in this table.

In the table retrieving these dates, you'll need a lookup formula. I've used a combination of MATCH and INDEX for the lookup job, andWEEKDAY plus the DATE function to construct the first date of the month for each holiday and determine the WEEKDAY number for that date.

In the table retrieving these dates, you'll need a lookup formula. I've used a combination of MATCH and INDEX for the lookup job, andWEEKDAY plus the DATE function to construct the first date of the month for each holiday and determine the WEEKDAY number for that date.

The 'Dates" shown in the larger table are text values, rather than actual Date and Time values. The MATCH/INDEX combination here returns only the day number from Table 2. The Month name is added with the first part of the formula. Formulas in ech column are he same with the exception of the month name and the indexed column from which the day nuber is returned.

Regards,

Barry