Show nth occurence of a weekday in a month

Location: http://www.mvps.org/dmcritchie/excel/datecalc02.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames] This is a continuation of the datecalc.htm which itself is a continueation of my Date and Time webpage.

An example utilizing Chip Pearson's
VBA Procedures For Dates And Times
http://www.cpearson.com/excel/DateTimeVBA.htm#NDow
look for topic "Nth Day Of Week" .

Examples:
=NthDayOfWeek(Year, Month, Nth occurence, day of week)

To Find the 3rd Tuesday[3] of the month of Oct [10] in 2003
=nthdayofweek(2003, 10, 3, 3) -- format as ddd mmm dd, yyyy

To see if there is a 5th Tuesday in Oct that is not the last day of the month (one long formula watch for wrapping)

=IF(MONTH(personal.xls!nthdayofweek(2003, 10, 5, 3)+1)=B28,personal.xls!nthdayofweek(2003, 10, 5, 3),"")

In case you are wondering about why it is because club meets on 1st Wednesday, 3rd Tuesday, and if not the last day of the month on the 5th Tuesday. If Tuesday was the last day of month the test prevents scheduling two meetings in the same week.

	A	B	C	D	E	F
1	Yr	Mo	N	dow	Result	Formula
2	2003	1	1	4	Wed 2003-01-01	=nthdayofweek(A2,B2,C2,D2)
3	2003	1	3	3	Tue 2003-01-21	=nthdayofweek(A3,B3,C3,D3)
4	2003	1	5	3		=IF(MONTH(nthdayofweek(A4,B4,C4,D4)+1)=B4,nthdayofweek(A4,B4,C4,D4),"")
5	2003	2	1	4	Wed 2003-02-05	=IF(MONTH(nthdayofweek(A5,B5,C5,D5)+1)=B5,nthdayofweek(A5,B5,C5,D5),"")
6	2003	2	3	3	Tue 2003-02-18	=IF(MONTH(nthdayofweek(A6,B6,C6,D6)+1)=B6,nthdayofweek(A6,B6,C6,D6),"")
7	2003	2	5	3		=IF(MONTH(nthdayofweek(A7,B7,C7,D7)+1)=B7,nthdayofweek(A7,B7,C7,D7),"")
8	2003	3	3	4	Wed 2003-03-19	=IF(MONTH(nthdayofweek(A8,B8,C8,D8)+1)=B8,nthdayofweek(A8,B8,C8,D8),"")
9	2003	3	1	3	Tue 2003-03-04	=IF(MONTH(nthdayofweek(A9,B9,C9,D9)+1)=B9,nthdayofweek(A9,B9,C9,D9),"")
10	2003	3	5	3		=IF(MONTH(nthdayofweek(A10,B10,C10,D10)+1)=B10,nthdayofweek(A10,B10,C10,D10),"")
11	2003	4	1	4	Wed 2003-04-02	=IF(MONTH(nthdayofweek(A11,B11,C11,D11)+1)=B11,nthdayofweek(A11,B11,C11,D11),"")
12	2003	4	3	3	Tue 2003-04-15	=IF(MONTH(nthdayofweek(A12,B12,C12,D12)+1)=B12,nthdayofweek(A12,B12,C12,D12),"")
13	2003	4	5	3	Tue 2003-04-29	=IF(MONTH(nthdayofweek(A13,B13,C13,D13)+1)=B13,nthdayofweek(A13,B13,C13,D13),"")
14	2003	5	1	4	Wed 2003-05-07	=IF(MONTH(nthdayofweek(A14,B14,C14,D14)+1)=B14,nthdayofweek(A14,B14,C14,D14),"")
15	2003	5	3	3	Tue 2003-05-20	=IF(MONTH(nthdayofweek(A15,B15,C15,D15)+1)=B15,nthdayofweek(A15,B15,C15,D15),"")
16	2002	5	5	3		=IF(MONTH(nthdayofweek(A16,B16,C16,D16)+1)=B16,nthdayofweek(A16,B16,C16,D16),"")
17	2003	6	1	4	Wed 2003-06-04	=IF(MONTH(nthdayofweek(A17,B17,C17,D17)+1)=B17,nthdayofweek(A17,B17,C17,D17),"")
18	2003	6	3	3	Tue 2003-06-17	=IF(MONTH(nthdayofweek(A18,B18,C18,D18)+1)=B18,nthdayofweek(A18,B18,C18,D18),"")
19	2003	6	5	3		=IF(MONTH(nthdayofweek(A19,B19,C19,D19)+1)=B19,nthdayofweek(A19,B19,C19,D19),"")
20	2003	7	1	4	Wed 2003-07-02	=IF(MONTH(nthdayofweek(A20,B20,C20,D20)+1)=B20,nthdayofweek(A20,B20,C20,D20),"")
21	2003	7	3	3	Tue 2003-07-15	=IF(MONTH(nthdayofweek(A21,B21,C21,D21)+1)=B21,nthdayofweek(A21,B21,C21,D21),"")
22	2003	7	5	3	Tue 2003-07-29	=IF(MONTH(nthdayofweek(A22,B22,C22,D22)+1)=B22,nthdayofweek(A22,B22,C22,D22),"")
23	2003	8	1	4	Wed 2003-08-06	=IF(MONTH(nthdayofweek(A23,B23,C23,D23)+1)=B23,nthdayofweek(A23,B23,C23,D23),"")
24	2003	8	3	3	Tue 2003-08-19	=IF(MONTH(nthdayofweek(A24,B24,C24,D24)+1)=B24,nthdayofweek(A24,B24,C24,D24),"")
25	2003	8	5	3		=IF(MONTH(nthdayofweek(A25,B25,C25,D25)+1)=B25,nthdayofweek(A25,B25,C25,D25),"")
26	2003	9	1	4	Wed 2003-09-03	=IF(MONTH(nthdayofweek(A26,B26,C26,D26)+1)=B26,nthdayofweek(A26,B26,C26,D26),"")
27	2003	9	3	3	Tue 2003-09-16	=IF(MONTH(nthdayofweek(A27,B27,C27,D27)+1)=B27,nthdayofweek(A27,B27,C27,D27),"")
28	2003	9	5	3		=IF(MONTH(nthdayofweek(A28,B28,C28,D28)+1)=B28,nthdayofweek(A28,B28,C28,D28),"")
29	2003	10	1	4	Wed 2003-10-01	=IF(MONTH(nthdayofweek(A29,B29,C29,D29)+1)=B29,nthdayofweek(A29,B29,C29,D29),"")
30	2003	10	3	3	Tue 2003-10-21	=IF(MONTH(nthdayofweek(A30,B30,C30,D30)+1)=B30,nthdayofweek(A30,B30,C30,D30),"")
31	2003	10	5	3		=IF(MONTH(nthdayofweek(A31,B31,C31,D31)+1)=B31,nthdayofweek(A31,B31,C31,D31),"")
32	2003	11	1	4	Wed 2003-11-05	=IF(MONTH(nthdayofweek(A32,B32,C32,D32)+1)=B32,nthdayofweek(A32,B32,C32,D32),"")
33	2003	11	3	3	Tue 2003-11-18	=IF(MONTH(nthdayofweek(A33,B33,C33,D33)+1)=B33,nthdayofweek(A33,B33,C33,D33),"")
34	2003	11	5	3		=IF(MONTH(nthdayofweek(A34,B34,C34,D34)+1)=B34,nthdayofweek(A34,B34,C34,D34),"")
35	2003	12	1	4	Wed 2003-12-03	=IF(MONTH(nthdayofweek(A35,B35,C35,D35)+1)=B35,nthdayofweek(A35,B35,C35,D35),"")
36	2003	12	3	3	Tue 2003-12-16	=IF(MONTH(nthdayofweek(A36,B36,C36,D36)+1)=B36,nthdayofweek(A36,B36,C36,D36),"")
37	2003	12	5	3	Tue 2003-12-30	=IF(MONTH(nthdayofweek(A37,B37,C37,D37)+1)=B37,nthdayofweek(A37,B37,C37,D37),"")

This page was introduced on March 5, 2003.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie send email comments