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.

 

[Toadstool]

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.

Because you're looking for the Nth week then you're going to subtract a WEEKDAY, which can have a value from 1 to 7, so the Nth week will be 2-8, 9-15, 16-22, 23-29 and 30-36. If we're looking for the 4th Thursday we'll find it in that 23-29 range, but as we're actually going to subtract a number from 1 to 7 we're looking in a range 22-28.

Each of the ranges has a Mon, Tue, Wed, etc but we want to find which is the Thursday. WEEKDAY default returns a number from 1 to 7 for Sunday through Saturday so we're going to look in that first week, 2-8, based on the day we're looking for. WEEKDAY default for a Thursday returns a 5 but we actually want (I want to say reciprocal but I'm sure that's the wrong mathematical word) so we subtract the 5 from 8 to get 3. Now we get the weekday of November 3rd which is 4 and subtract that from the 29 to get the 25th.

ExcelTeacher21.xlsx
	G	H	I	J	K	L	M
3	Ordinal	Day of Week	WEEKDAY DoW	Year	Month		Result
4	4	Thu	5	2021	11		25-Nov-21
Sheet1

Cell Formulas
Range	Formula
I4	I4	=INT(FIND(H4,"SunMonTueWedThuFriSat")/3)+1
M4	M4	=DATE(J4,K4,1+(7*G4)-WEEKDAY(DATE(J4,K4,8-I4)))

Cells with Data Validation
Cell	Allow	Criteria
G4	List	1,2,3,4,5
H4	List	Sun,Mon,Tue,Wed,Thu,Fri,Sat

If you want to set a test then ask how you would change the formula to find the 5th Tuesday but using WEEKDAY with a return_type of 3 (where it returns 0 through 6 for Monday through Sunday).

Hint:

=(7*5)-WEEKDAY(DATE(2021,11,7-1),3)