Date Patterns - Day of the Month Date - Is it Possible to…?

Question

Date Patterns - Day of the Month Date - Is it Possible to…?

I’m interested in date patterns; this request focuses on holidays.

There are two kinds of holidays…

Set-date-in-the month, like

New Years Day being January 1 every year
Independence Day being on July 4 every year
Christmas being on December 25 every year.

<unsure the right term> Set day-of-the-week in the month

Dr. Martin Luther King Jr. Day - third Monday in January
Memorial Day - last Monday in May
Labor Day - first Monday in September
Thanksgiving - fourth Thursday in November

I’m interested in the patterns of years of the days-of-the week of the holidays that are set days-of-the-week in the month.

Ex.

How often is Memorial Day May 31 (as it was this year (2021), making pool time and end-of-school seem much later than usual)

How often is Thanksgiving on Nov. 22?

Are there any formulas in Numbers that lets you do this?

I’m seeking something like

Example 1… Memorial Day

=<TBD formula>[last Monday in May],<type in the year, like “2021”>

and have the value of the cell be

Monday, May 31, 2021

then I could apply the next cell (down or right…)

=EDATE(<first date>,12) and that cell would show the date the next year

Monday, May 30, 2022

Example 2… Thanksgiving

=<TBD formula>[4th Thursday in November],<type in the year, like “2018”>

and have the value of the cell be

Thursday, November 22, 2018

then I could apply the next cell (down or right…)

=EDATE(<first date>,12) and that cell would show the date the next year

Thursday, November 28, 2019

Any/all help will be appreciated.

Thanks,

Michael

Posted on Sep 1, 2021 7:31 PM

Reply

Answer 1

Badunit

Level 7

30,954 points

Sep 2, 2021 5:26 AM in response to Michael VanVooren

You can do something like this

Formulas in Table 1 are

C2 =IFERROR(Table 2::C3,"")

D4 =IF(C3<>"",C3−C2,"")

Fill down with those two formulas to complete the columns

Formulas in Table 2 are

A1 =Table 1::B2

A2 =Table 1::B3

A3 =EDATE(A2,12)

Fill down with or copy/paste formula from A3 to complete the column

C3 =IF(DAYNAME(A3)=A$1,YEAR(A3),"")

D3 =SMALL(B,ROW()−2)

Fill down with C3 and D3 to complete the columns

My table is much longer than in the screenshot. I meant to clip it mid-row to indicate that but I guess I missed.

If you want to add more rows at the bottom to increase the range of dates, the new rows will automatically get the formulas.

Reply

Date Patterns - Day of the Month Date - Is it Possible to…?

Similar questions