Formula to calculate day of the month

[Excelteacher21]

Please help a teacher. It's my first year teaching Excel. Found an article where it explains how to calculate Thanksgiving date in any given year using the formula =DATE(Y,M,1+7*Nth)-WEEKDAY(DATE(Y,M,8-DoW). I want to teach this to my students TOMORROW but can't remember why 1+7. Where do we get that or why do we use that? And where do we get the "8" in 8-DoW). All other parts of the formula I understand and can explain to my students. I passed certification in 2013 but my Excel is rusty. If anyone could answer those 2 questions, I'd appreciate it.

 

[Fluff]

Hi & welcome to MrExcel.
Not sure what the Nth or DoW represent, but I would simply use this formula

=WORKDAY.INTL(DATE(2021,12,1),-1,"1110111")

which will find the last Thursday of the month.

 

[Excelteacher21]

Hi & welcome to MrExcel.
Not sure what the Nth or DoW represent, but I would simply use this formula

=WORKDAY.INTL(DATE(2021,12,1),-1,"1110111")

which will find the last Thursday of the month.

Nth is a 1, 2, 3, or 4 for the first, second, third, or fourth day of the month. DoW is the weekday number with Sunday being 1, Monday being 2, etc. Code is not part of the curriculum, so I'd rather teach the formula, but thank you for your reply.

 

[Fluff]

What I suggested is a formula & not code.

 

[Toadstool]

Hi Excelteacher21,

So for Thanksgiving we want the fourth Thursday of November.

The first part of the formula calculates the last possible date range. The second part calculates what to subtract from that given the day of the week we're looking for.

If we substitute Nth in 1+(7*Nth) with the 5 possible values (never more than 5 days of the week in a month) then we get: 8, 15, 22, 29, 36. For the fourth we therefore get 1+(7*4)=29

WEEKDAY returns a number for the day of the week. The default is 1 for Sunday through 7 for Saturday, so if we're looking for a Thursday we use number 5, which gives the second part as -WEEKDAY(DATE(2021,11,8-5)) which resolves to -WEEKDAY(DATE(2021,11,3)) or the WEEKDAY of a Wednesday so it subtracts 4.

29-4=25 so Thanksgiving is 25 November in 2021.

We can also do this for the fifth Tuesday of the month as November 2021 contains 5 Tuesdays.

1+(7*5)=36

The default WEEKDAY for a Tuesday is 3, so -WEEKDAY(DATE(2021,11,8-DoW)) becomes -WEEKDAY(DATE(2021,11,8-3)), giving -WEEKDAY(DATE(2021,11,5)) and 5th November was a Friday so we subtract 6.

36-6=30 so the fifth Tuesday of November 2021 is 30 November 2021.

 

[Eric W]

I think Fluff's formula bears a bit more attention, since I think it's much easier to remember/explain. But Thanksgiving in the US is the 4th Thursday in November, not the last. Some years have 5 Thursdays. It was changed to allow more shopping days before Christmas. ? So I'd change the formula to:

=WORKDAY.INTL(DATE(2021,11,0),4,"1110111")

DATE(year,month,day) is just used to give the starting date value. For the day parameter, if you use 0, that's equivalent to saying, give me the last day of the previous month.

The WORKDAY.INTL function allows you to pick which days are workdays, and which are weekend (non-work) days. The "1110111" parameter says the first 3 days of the week (Monday, Tuesday, Wednesday) are non-work days, then Thursday (the 0 in the middle) is a work day, then the last 3 days (Friday, Saturday, Sunday) are non-work days.

Then the WORKDAY.INTL function just says, starting at October 31, give me the 4th work day. Since we've told it that only Thursdays are workdays, that's equivalent to saying, give me the 4th Thursday after October 31.

So a generic version of the formula is:

=WORKDAY.INTL(DATE(year,month,0),Nth,"1110111")

and for DoW, put a 0 in the last parameter, where the 1st position is Monday, etc.

 

[Fluff]

But Thanksgiving in the US is the 4th Thursday in November, not the last

Oops, didn't read the Wiki article far enough.

 

[Excelteacher21]

What I suggested is a formula & not code.

The CODE function is not part of my curriculum.

 

[Fluff]

I didn't use the Code function.

 

[Excelteacher21]

Hi Excelteacher21,

So for Thanksgiving we want the fourth Thursday of November.

The first part of the formula calculates the last possible date range. The second part calculates what to subtract from that given the day of the week we're looking for.

If we substitute Nth in 1+(7*Nth) with the 5 possible values (never more than 5 days of the week in a month) then we get: 8, 15, 22, 29, 36. For the fourth we therefore get 1+(7*4)=29

WEEKDAY returns a number for the day of the week. The default is 1 for Sunday through 7 for Saturday, so if we're looking for a Thursday we use number 5, which gives the second part as -WEEKDAY(DATE(2021,11,8-5)) which resolves to -WEEKDAY(DATE(2021,11,3)) or the WEEKDAY of a Wednesday so it subtracts 4.

29-4=25 so Thanksgiving is 25 November in 2021.

We can also do this for the fifth Tuesday of the month as November 2021 contains 5 Tuesdays.

1+(7*5)=36

The default WEEKDAY for a Tuesday is 3, so -WEEKDAY(DATE(2021,11,8-DoW)) becomes -WEEKDAY(DATE(2021,11,8-3)), giving -WEEKDAY(DATE(2021,11,5)) and 5th November was a Friday so we subtract 6.

36-6=30 so the fifth Tuesday of November 2021 is 30 November 2021.

Thanks! I get all that. What I don’t remember is where we get the “1+7. And in the WEEKDAY part, where do we get the “8”. Everything else makes sense to me.