Want to create a list of weekends excluding 3rd and 5th Saturday of every month

J

James Clear

Board Regular
Joined
Jul 12, 2021
Messages
139
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
Let us say I have 01- 01-2022 mentioned in A1

Now I want to create a list downside from A2 all dates of following saturdays n sundays excluding 3rd n 5th Saturday

Like
01- 01-2022
02-01-2022
08-01-2022
09-01-2022
16-01-2022
22-01-2022
23-01-2022
30-01-2022
05-02-2022
06-02-2022
12-02-2022
13-02-2022
20-02-2022

And so on

Please help me to build this formula
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
365 only...

Here it is in column A. All the rest I left in to show the steps unpacked.

MrExcelPlayground10.xlsx
ABCDEFGHI
11/1/2022
2Sunday, January 2, 20221Sunday, January 2, 2022Sunday, January 2, 2022Saturday, January 1, 2022Sunday, January 2, 2022Sunday, January 2, 2022Sunday, January 2, 2022Sunday, January 2, 2022
3Saturday, January 8, 20222Monday, January 3, 2022#VALUE!#VALUE!Saturday, January 8, 2022Saturday, January 8, 2022
4Sunday, January 9, 20223Tuesday, January 4, 2022#VALUE!#VALUE!Sunday, January 9, 2022Sunday, January 9, 2022
5Sunday, January 16, 20224Wednesday, January 5, 2022#VALUE!#VALUE!Sunday, January 16, 2022Sunday, January 16, 2022
6Saturday, January 22, 20225Thursday, January 6, 2022#VALUE!#VALUE!Saturday, January 22, 2022Saturday, January 22, 2022
7Sunday, January 23, 20226Friday, January 7, 2022#VALUE!#VALUE!Sunday, January 23, 2022Sunday, January 23, 2022
8Sunday, January 30, 20227Saturday, January 8, 2022Saturday, January 8, 2022Saturday, January 1, 2022Saturday, January 8, 2022Saturday, January 8, 2022Sunday, January 30, 2022Sunday, January 30, 2022
9Saturday, February 5, 20228Sunday, January 9, 2022Sunday, January 9, 2022Saturday, January 1, 2022Sunday, January 9, 2022Sunday, January 9, 2022Saturday, February 5, 2022Saturday, February 5, 2022
10Sunday, February 6, 20229Monday, January 10, 2022#VALUE!#VALUE!Sunday, February 6, 2022Sunday, February 6, 2022
11Saturday, February 12, 202210Tuesday, January 11, 2022#VALUE!#VALUE!Saturday, February 12, 2022Saturday, February 12, 2022
12Sunday, February 13, 202211Wednesday, January 12, 2022#VALUE!#VALUE!Sunday, February 13, 2022Sunday, February 13, 2022
13Sunday, February 20, 202212Thursday, January 13, 2022#VALUE!#VALUE!Sunday, February 20, 2022Sunday, February 20, 2022
14Saturday, February 26, 202213Friday, January 14, 2022#VALUE!#VALUE!Saturday, February 26, 2022Saturday, February 26, 2022
15Sunday, February 27, 202214Saturday, January 15, 2022Saturday, January 15, 2022Saturday, January 1, 2022Sunday, February 27, 2022Sunday, February 27, 2022
16Saturday, March 5, 202215Sunday, January 16, 2022Sunday, January 16, 2022Saturday, January 1, 2022Sunday, January 16, 2022Sunday, January 16, 2022Saturday, March 5, 2022Saturday, March 5, 2022
17Sunday, March 6, 202216Monday, January 17, 2022#VALUE!#VALUE!Sunday, March 6, 2022Sunday, March 6, 2022
18Saturday, March 12, 202217Tuesday, January 18, 2022#VALUE!#VALUE!Saturday, March 12, 2022Saturday, March 12, 2022
19Sunday, March 13, 202218Wednesday, January 19, 2022#VALUE!#VALUE!Sunday, March 13, 2022Sunday, March 13, 2022
20Sunday, March 20, 202219Thursday, January 20, 2022#VALUE!#VALUE!Sunday, March 20, 2022Sunday, March 20, 2022
21Saturday, March 26, 202220Friday, January 21, 2022#VALUE!#VALUE!Saturday, March 26, 2022Saturday, March 26, 2022
22Sunday, March 27, 202221Saturday, January 22, 2022Saturday, January 22, 2022Saturday, January 1, 2022Saturday, January 22, 2022Saturday, January 22, 2022Sunday, March 27, 2022Sunday, March 27, 2022
23Saturday, April 2, 202222Sunday, January 23, 2022Sunday, January 23, 2022Saturday, January 1, 2022Sunday, January 23, 2022Sunday, January 23, 2022Saturday, April 2, 2022Saturday, April 2, 2022
24Sunday, April 3, 202223Monday, January 24, 2022#VALUE!#VALUE!Sunday, April 3, 2022Sunday, April 3, 2022
25Saturday, April 9, 202224Tuesday, January 25, 2022#VALUE!#VALUE!Saturday, April 9, 2022Saturday, April 9, 2022
26Sunday, April 10, 202225Wednesday, January 26, 2022#VALUE!#VALUE!Sunday, April 10, 2022Sunday, April 10, 2022
27Sunday, April 17, 202226Thursday, January 27, 2022#VALUE!#VALUE!Sunday, April 17, 2022Sunday, April 17, 2022
28Saturday, April 23, 202227Friday, January 28, 2022#VALUE!#VALUE!Saturday, April 23, 2022Saturday, April 23, 2022
29Sunday, April 24, 202228Saturday, January 29, 2022Saturday, January 29, 2022Saturday, January 1, 2022Sunday, April 24, 2022Sunday, April 24, 2022
30Sunday, May 1, 202229Sunday, January 30, 2022Sunday, January 30, 2022Saturday, January 1, 2022Sunday, January 30, 2022Sunday, January 30, 2022Sunday, May 1, 2022Sunday, May 1, 2022
31Saturday, May 7, 202230Monday, January 31, 2022#VALUE!#VALUE!Saturday, May 7, 2022Saturday, May 7, 2022
32Sunday, May 8, 202231Tuesday, February 1, 2022#VALUE!#VALUE!Sunday, May 8, 2022Sunday, May 8, 2022
33Saturday, May 14, 202232Wednesday, February 2, 2022#VALUE!#VALUE!Saturday, May 14, 2022Saturday, May 14, 2022
34Sunday, May 15, 202233Thursday, February 3, 2022#VALUE!#VALUE!Sunday, May 15, 2022Sunday, May 15, 2022
35Sunday, May 22, 202234Friday, February 4, 2022#VALUE!#VALUE!Sunday, May 22, 2022Sunday, May 22, 2022
36Saturday, May 28, 202235Saturday, February 5, 2022Saturday, February 5, 2022Tuesday, February 1, 2022Saturday, February 5, 2022Saturday, February 5, 2022Saturday, May 28, 2022Saturday, May 28, 2022
37Sunday, May 29, 202236Sunday, February 6, 2022Sunday, February 6, 2022Tuesday, February 1, 2022Sunday, February 6, 2022Sunday, February 6, 2022Sunday, May 29, 2022Sunday, May 29, 2022
38Saturday, June 4, 202237Monday, February 7, 2022#VALUE!#VALUE!Saturday, June 4, 2022Saturday, June 4, 2022
39Sunday, June 5, 202238Tuesday, February 8, 2022#VALUE!#VALUE!Sunday, June 5, 2022Sunday, June 5, 2022
40Saturday, June 11, 202239Wednesday, February 9, 2022#VALUE!#VALUE!Saturday, June 11, 2022Saturday, June 11, 2022
41Sunday, June 12, 202240Thursday, February 10, 2022#VALUE!#VALUE!Sunday, June 12, 2022Sunday, June 12, 2022
42Sunday, June 19, 202241Friday, February 11, 2022#VALUE!#VALUE!Sunday, June 19, 2022Sunday, June 19, 2022
43Saturday, June 25, 202242Saturday, February 12, 2022Saturday, February 12, 2022Tuesday, February 1, 2022Saturday, February 12, 2022Saturday, February 12, 2022Saturday, June 25, 2022Saturday, June 25, 2022
44Sunday, June 26, 202243Sunday, February 13, 2022Sunday, February 13, 2022Tuesday, February 1, 2022Sunday, February 13, 2022Sunday, February 13, 2022Sunday, June 26, 2022Sunday, June 26, 2022
45Saturday, July 2, 202244Monday, February 14, 2022#VALUE!#VALUE!Saturday, July 2, 2022Saturday, July 2, 2022
46Sunday, July 3, 202245Tuesday, February 15, 2022#VALUE!#VALUE!Sunday, July 3, 2022Sunday, July 3, 2022
47Saturday, July 9, 202246Wednesday, February 16, 2022#VALUE!#VALUE!Saturday, July 9, 2022Saturday, July 9, 2022
48Sunday, July 10, 202247Thursday, February 17, 2022#VALUE!#VALUE!Sunday, July 10, 2022Sunday, July 10, 2022
49Sunday, July 17, 202248Friday, February 18, 2022#VALUE!#VALUE!Sunday, July 17, 2022Sunday, July 17, 2022
50Saturday, July 23, 202249Saturday, February 19, 2022Saturday, February 19, 2022Tuesday, February 1, 2022Saturday, July 23, 2022Saturday, July 23, 2022
51Sunday, July 24, 202250Sunday, February 20, 2022Sunday, February 20, 2022Tuesday, February 1, 2022Sunday, February 20, 2022Sunday, February 20, 2022Sunday, July 24, 2022Sunday, July 24, 2022
52Sunday, July 31, 202251Monday, February 21, 2022#VALUE!#VALUE!Sunday, July 31, 2022Sunday, July 31, 2022
53Saturday, August 6, 202252Tuesday, February 22, 2022#VALUE!#VALUE!Saturday, August 6, 2022Saturday, August 6, 2022
54Sunday, August 7, 202253Wednesday, February 23, 2022#VALUE!#VALUE!Sunday, August 7, 2022Sunday, August 7, 2022
55Saturday, August 13, 202254Thursday, February 24, 2022#VALUE!#VALUE!Saturday, August 13, 2022Saturday, August 13, 2022
56Sunday, August 14, 202255Friday, February 25, 2022#VALUE!#VALUE!Sunday, August 14, 2022Sunday, August 14, 2022
57Sunday, August 21, 202256Saturday, February 26, 2022Saturday, February 26, 2022Tuesday, February 1, 2022Saturday, February 26, 2022Saturday, February 26, 2022Sunday, August 21, 2022Sunday, August 21, 2022
58Saturday, August 27, 202257Sunday, February 27, 2022Sunday, February 27, 2022Tuesday, February 1, 2022Sunday, February 27, 2022Sunday, February 27, 2022Saturday, August 27, 2022Saturday, August 27, 2022
59Sunday, August 28, 202258Monday, February 28, 2022#VALUE!#VALUE!Sunday, August 28, 2022Sunday, August 28, 2022
60Saturday, September 3, 202259Tuesday, March 1, 2022#VALUE!#VALUE!Saturday, September 3, 2022Saturday, September 3, 2022
61Sunday, September 4, 202260Wednesday, March 2, 2022#VALUE!#VALUE!Sunday, September 4, 2022Sunday, September 4, 2022
62Saturday, September 10, 202261Thursday, March 3, 2022#VALUE!#VALUE!Saturday, September 10, 2022Saturday, September 10, 2022
63Sunday, September 11, 202262Friday, March 4, 2022#VALUE!#VALUE!Sunday, September 11, 2022Sunday, September 11, 2022
64Sunday, September 18, 202263Saturday, March 5, 2022Saturday, March 5, 2022Tuesday, March 1, 2022Saturday, March 5, 2022Saturday, March 5, 2022Sunday, September 18, 2022Sunday, September 18, 2022
65Saturday, September 24, 202264Sunday, March 6, 2022Sunday, March 6, 2022Tuesday, March 1, 2022Sunday, March 6, 2022Sunday, March 6, 2022Saturday, September 24, 2022Saturday, September 24, 2022
66Sunday, September 25, 202265Monday, March 7, 2022#VALUE!#VALUE!Sunday, September 25, 2022Sunday, September 25, 2022
67Saturday, October 1, 202266Tuesday, March 8, 2022#VALUE!#VALUE!Saturday, October 1, 2022Saturday, October 1, 2022
68Sunday, October 2, 202267Wednesday, March 9, 2022#VALUE!#VALUE!Sunday, October 2, 2022Sunday, October 2, 2022
69Saturday, October 8, 202268Thursday, March 10, 2022#VALUE!#VALUE!Saturday, October 8, 2022Saturday, October 8, 2022
70Sunday, October 9, 202269Friday, March 11, 2022#VALUE!#VALUE!Sunday, October 9, 2022Sunday, October 9, 2022
71Sunday, October 16, 202270Saturday, March 12, 2022Saturday, March 12, 2022Tuesday, March 1, 2022Saturday, March 12, 2022Saturday, March 12, 2022Sunday, October 16, 2022Sunday, October 16, 2022
72Saturday, October 22, 202271Sunday, March 13, 2022Sunday, March 13, 2022Tuesday, March 1, 2022Sunday, March 13, 2022Sunday, March 13, 2022Saturday, October 22, 2022Saturday, October 22, 2022
73Sunday, October 23, 202272Monday, March 14, 2022#VALUE!#VALUE!Sunday, October 23, 2022Sunday, October 23, 2022
74Sunday, October 30, 202273Tuesday, March 15, 2022#VALUE!#VALUE!Sunday, October 30, 2022Sunday, October 30, 2022
75Saturday, November 5, 202274Wednesday, March 16, 2022#VALUE!#VALUE!Saturday, November 5, 2022Saturday, November 5, 2022
76Sunday, November 6, 202275Thursday, March 17, 2022#VALUE!#VALUE!Sunday, November 6, 2022Sunday, November 6, 2022
77Saturday, November 12, 202276Friday, March 18, 2022#VALUE!#VALUE!Saturday, November 12, 2022Saturday, November 12, 2022
78Sunday, November 13, 202277Saturday, March 19, 2022Saturday, March 19, 2022Tuesday, March 1, 2022Sunday, November 13, 2022Sunday, November 13, 2022
79Sunday, November 20, 202278Sunday, March 20, 2022Sunday, March 20, 2022Tuesday, March 1, 2022Sunday, March 20, 2022Sunday, March 20, 2022Sunday, November 20, 2022Sunday, November 20, 2022
80Saturday, November 26, 202279Monday, March 21, 2022#VALUE!#VALUE!Saturday, November 26, 2022Saturday, November 26, 2022
81Sunday, November 27, 202280Tuesday, March 22, 2022#VALUE!#VALUE!Sunday, November 27, 2022Sunday, November 27, 2022
82Saturday, December 3, 202281Wednesday, March 23, 2022#VALUE!#VALUE!Saturday, December 3, 2022Saturday, December 3, 2022
83Sunday, December 4, 202282Thursday, March 24, 2022#VALUE!#VALUE!Sunday, December 4, 2022Sunday, December 4, 2022
84Saturday, December 10, 202283Friday, March 25, 2022#VALUE!#VALUE!Saturday, December 10, 2022Saturday, December 10, 2022
85Sunday, December 11, 202284Saturday, March 26, 2022Saturday, March 26, 2022Tuesday, March 1, 2022Saturday, March 26, 2022Saturday, March 26, 2022Sunday, December 11, 2022Sunday, December 11, 2022
86Sunday, December 18, 202285Sunday, March 27, 2022Sunday, March 27, 2022Tuesday, March 1, 2022Sunday, March 27, 2022Sunday, March 27, 2022Sunday, December 18, 2022Sunday, December 18, 2022
87Saturday, December 24, 202286Monday, March 28, 2022#VALUE!#VALUE!Saturday, December 24, 2022Saturday, December 24, 2022
88Sunday, December 25, 202287Tuesday, March 29, 2022#VALUE!#VALUE!Sunday, December 25, 2022Sunday, December 25, 2022
89Sunday, January 1, 202388Wednesday, March 30, 2022#VALUE!#VALUE!Sunday, January 1, 2023Sunday, January 1, 2023
Sheet15
Cell Formulas
RangeFormula
A2:A89A2=LET(a,SEQUENCE(365),b,A1+a,c,IF(WEEKDAY(b,2)<6,"",b),d,DATE(YEAR(c),MONTH(c),1),e,c-d,f,IF(WEEKDAY(c,2)<>6,c,IF(e>27,"",IF(e>13,IF(e<21,"",c),c))),g,IFERROR(f,""),h,SORT(g),INDEX(h,SEQUENCE(COUNT(h))))
B2:B366B2=SEQUENCE(365)
C2:C366C2=$A$1+B2#
D2:D366D2=IF(WEEKDAY(C2#,2)<6,"",C2#)
E2:E366E2=DATE(YEAR(D2#),MONTH(D2#),1)
F2:F366F2=IF(WEEKDAY(D2#,2)<>6,D2#,IF(D2#-E2#>27,"",IF(D2#-E2#>13,IF(D2#-E2#<21,"",D2#),D2#)))
G2:G366G2=IFERROR(F2#,"")
H2:H366H2=SORT(G2#)
I2:I89I2=INDEX(H2#,SEQUENCE(COUNT(H2#)))
Dynamic array formulas.
 
Upvote 0
Another option
Excel Formula: 
=LET(r,NETWORKDAYS.INTL(A1,EOMONTH(A1,11),"1111100"),d,WORKDAY.INTL(A1,SEQUENCE(r,,0),"1111100"),dd,DAY(d)-1,x,IF(WEEKDAY(d)=1,d,IF(dd>27,"",IF(dd>13,IF(dd<21,"",d),d))),FILTER(x,x<>""))
 
Upvote 0
I just borrowed your idea for excluding the Saturdays, but used workday.intl to slim down the calculation a bit.
 
Upvote 0
From a hacksaw to a scalpel. I love looking at further solutions - that's were some gold really is for me. I'm baffled by my use of INDEX in this problem to filter the data down to size.

I tend to avoid VBA questions on here (and in my own excel life since Office365) - But, I'm always amazed by the brevity of the code here, compared to my old bulky stuff.
 
Upvote 0
Rather than sorting the data & using Index, I would just use filter
Excel Formula: 
=LET(a,SEQUENCE(365),b,A1+a,c,IF(WEEKDAY(b,2)<6,"",b),d,DATE(YEAR(c),MONTH(c),1),e,c-d,f,IF(WEEKDAY(c,2)<>6,c,IF(e>27,"",IF(e>13,IF(e<21,"",c),c))),g,IFERROR(f,""),FILTER(g,g<>""))
 
Upvote 0
JamesCanale said:
365 only...

Here it is in column A. All the rest I left in to show the steps unpacked.

MrExcelPlayground10.xlsx
ABCDEFGHI
11/1/2022
2Sunday, January 2, 20221Sunday, January 2, 2022Sunday, January 2, 2022Saturday, January 1, 2022Sunday, January 2, 2022Sunday, January 2, 2022Sunday, January 2, 2022Sunday, January 2, 2022
3Saturday, January 8, 20222Monday, January 3, 2022#VALUE!#VALUE!Saturday, January 8, 2022Saturday, January 8, 2022
4Sunday, January 9, 20223Tuesday, January 4, 2022#VALUE!#VALUE!Sunday, January 9, 2022Sunday, January 9, 2022
5Sunday, January 16, 20224Wednesday, January 5, 2022#VALUE!#VALUE!Sunday, January 16, 2022Sunday, January 16, 2022
6Saturday, January 22, 20225Thursday, January 6, 2022#VALUE!#VALUE!Saturday, January 22, 2022Saturday, January 22, 2022
7Sunday, January 23, 20226Friday, January 7, 2022#VALUE!#VALUE!Sunday, January 23, 2022Sunday, January 23, 2022
8Sunday, January 30, 20227Saturday, January 8, 2022Saturday, January 8, 2022Saturday, January 1, 2022Saturday, January 8, 2022Saturday, January 8, 2022Sunday, January 30, 2022Sunday, January 30, 2022
9Saturday, February 5, 20228Sunday, January 9, 2022Sunday, January 9, 2022Saturday, January 1, 2022Sunday, January 9, 2022Sunday, January 9, 2022Saturday, February 5, 2022Saturday, February 5, 2022
10Sunday, February 6, 20229Monday, January 10, 2022#VALUE!#VALUE!Sunday, February 6, 2022Sunday, February 6, 2022
11Saturday, February 12, 202210Tuesday, January 11, 2022#VALUE!#VALUE!Saturday, February 12, 2022Saturday, February 12, 2022
12Sunday, February 13, 202211Wednesday, January 12, 2022#VALUE!#VALUE!Sunday, February 13, 2022Sunday, February 13, 2022
13Sunday, February 20, 202212Thursday, January 13, 2022#VALUE!#VALUE!Sunday, February 20, 2022Sunday, February 20, 2022
14Saturday, February 26, 202213Friday, January 14, 2022#VALUE!#VALUE!Saturday, February 26, 2022Saturday, February 26, 2022
15Sunday, February 27, 202214Saturday, January 15, 2022Saturday, January 15, 2022Saturday, January 1, 2022Sunday, February 27, 2022Sunday, February 27, 2022
16Saturday, March 5, 202215Sunday, January 16, 2022Sunday, January 16, 2022Saturday, January 1, 2022Sunday, January 16, 2022Sunday, January 16, 2022Saturday, March 5, 2022Saturday, March 5, 2022
17Sunday, March 6, 202216Monday, January 17, 2022#VALUE!#VALUE!Sunday, March 6, 2022Sunday, March 6, 2022
18Saturday, March 12, 202217Tuesday, January 18, 2022#VALUE!#VALUE!Saturday, March 12, 2022Saturday, March 12, 2022
19Sunday, March 13, 202218Wednesday, January 19, 2022#VALUE!#VALUE!Sunday, March 13, 2022Sunday, March 13, 2022
20Sunday, March 20, 202219Thursday, January 20, 2022#VALUE!#VALUE!Sunday, March 20, 2022Sunday, March 20, 2022
21Saturday, March 26, 202220Friday, January 21, 2022#VALUE!#VALUE!Saturday, March 26, 2022Saturday, March 26, 2022
22Sunday, March 27, 202221Saturday, January 22, 2022Saturday, January 22, 2022Saturday, January 1, 2022Saturday, January 22, 2022Saturday, January 22, 2022Sunday, March 27, 2022Sunday, March 27, 2022
23Saturday, April 2, 202222Sunday, January 23, 2022Sunday, January 23, 2022Saturday, January 1, 2022Sunday, January 23, 2022Sunday, January 23, 2022Saturday, April 2, 2022Saturday, April 2, 2022
24Sunday, April 3, 202223Monday, January 24, 2022#VALUE!#VALUE!Sunday, April 3, 2022Sunday, April 3, 2022
25Saturday, April 9, 202224Tuesday, January 25, 2022#VALUE!#VALUE!Saturday, April 9, 2022Saturday, April 9, 2022
26Sunday, April 10, 202225Wednesday, January 26, 2022#VALUE!#VALUE!Sunday, April 10, 2022Sunday, April 10, 2022
27Sunday, April 17, 202226Thursday, January 27, 2022#VALUE!#VALUE!Sunday, April 17, 2022Sunday, April 17, 2022
28Saturday, April 23, 202227Friday, January 28, 2022#VALUE!#VALUE!Saturday, April 23, 2022Saturday, April 23, 2022
29Sunday, April 24, 202228Saturday, January 29, 2022Saturday, January 29, 2022Saturday, January 1, 2022Sunday, April 24, 2022Sunday, April 24, 2022
30Sunday, May 1, 202229Sunday, January 30, 2022Sunday, January 30, 2022Saturday, January 1, 2022Sunday, January 30, 2022Sunday, January 30, 2022Sunday, May 1, 2022Sunday, May 1, 2022
31Saturday, May 7, 202230Monday, January 31, 2022#VALUE!#VALUE!Saturday, May 7, 2022Saturday, May 7, 2022
32Sunday, May 8, 202231Tuesday, February 1, 2022#VALUE!#VALUE!Sunday, May 8, 2022Sunday, May 8, 2022
33Saturday, May 14, 202232Wednesday, February 2, 2022#VALUE!#VALUE!Saturday, May 14, 2022Saturday, May 14, 2022
34Sunday, May 15, 202233Thursday, February 3, 2022#VALUE!#VALUE!Sunday, May 15, 2022Sunday, May 15, 2022
35Sunday, May 22, 202234Friday, February 4, 2022#VALUE!#VALUE!Sunday, May 22, 2022Sunday, May 22, 2022
36Saturday, May 28, 202235Saturday, February 5, 2022Saturday, February 5, 2022Tuesday, February 1, 2022Saturday, February 5, 2022Saturday, February 5, 2022Saturday, May 28, 2022Saturday, May 28, 2022
37Sunday, May 29, 202236Sunday, February 6, 2022Sunday, February 6, 2022Tuesday, February 1, 2022Sunday, February 6, 2022Sunday, February 6, 2022Sunday, May 29, 2022Sunday, May 29, 2022
38Saturday, June 4, 202237Monday, February 7, 2022#VALUE!#VALUE!Saturday, June 4, 2022Saturday, June 4, 2022
39Sunday, June 5, 202238Tuesday, February 8, 2022#VALUE!#VALUE!Sunday, June 5, 2022Sunday, June 5, 2022
40Saturday, June 11, 202239Wednesday, February 9, 2022#VALUE!#VALUE!Saturday, June 11, 2022Saturday, June 11, 2022
41Sunday, June 12, 202240Thursday, February 10, 2022#VALUE!#VALUE!Sunday, June 12, 2022Sunday, June 12, 2022
42Sunday, June 19, 202241Friday, February 11, 2022#VALUE!#VALUE!Sunday, June 19, 2022Sunday, June 19, 2022
43Saturday, June 25, 202242Saturday, February 12, 2022Saturday, February 12, 2022Tuesday, February 1, 2022Saturday, February 12, 2022Saturday, February 12, 2022Saturday, June 25, 2022Saturday, June 25, 2022
44Sunday, June 26, 202243Sunday, February 13, 2022Sunday, February 13, 2022Tuesday, February 1, 2022Sunday, February 13, 2022Sunday, February 13, 2022Sunday, June 26, 2022Sunday, June 26, 2022
45Saturday, July 2, 202244Monday, February 14, 2022#VALUE!#VALUE!Saturday, July 2, 2022Saturday, July 2, 2022
46Sunday, July 3, 202245Tuesday, February 15, 2022#VALUE!#VALUE!Sunday, July 3, 2022Sunday, July 3, 2022
47Saturday, July 9, 202246Wednesday, February 16, 2022#VALUE!#VALUE!Saturday, July 9, 2022Saturday, July 9, 2022
48Sunday, July 10, 202247Thursday, February 17, 2022#VALUE!#VALUE!Sunday, July 10, 2022Sunday, July 10, 2022
49Sunday, July 17, 202248Friday, February 18, 2022#VALUE!#VALUE!Sunday, July 17, 2022Sunday, July 17, 2022
50Saturday, July 23, 202249Saturday, February 19, 2022Saturday, February 19, 2022Tuesday, February 1, 2022Saturday, July 23, 2022Saturday, July 23, 2022
51Sunday, July 24, 202250Sunday, February 20, 2022Sunday, February 20, 2022Tuesday, February 1, 2022Sunday, February 20, 2022Sunday, February 20, 2022Sunday, July 24, 2022Sunday, July 24, 2022
52Sunday, July 31, 202251Monday, February 21, 2022#VALUE!#VALUE!Sunday, July 31, 2022Sunday, July 31, 2022
53Saturday, August 6, 202252Tuesday, February 22, 2022#VALUE!#VALUE!Saturday, August 6, 2022Saturday, August 6, 2022
54Sunday, August 7, 202253Wednesday, February 23, 2022#VALUE!#VALUE!Sunday, August 7, 2022Sunday, August 7, 2022
55Saturday, August 13, 202254Thursday, February 24, 2022#VALUE!#VALUE!Saturday, August 13, 2022Saturday, August 13, 2022
56Sunday, August 14, 202255Friday, February 25, 2022#VALUE!#VALUE!Sunday, August 14, 2022Sunday, August 14, 2022
57Sunday, August 21, 202256Saturday, February 26, 2022Saturday, February 26, 2022Tuesday, February 1, 2022Saturday, February 26, 2022Saturday, February 26, 2022Sunday, August 21, 2022Sunday, August 21, 2022
58Saturday, August 27, 202257Sunday, February 27, 2022Sunday, February 27, 2022Tuesday, February 1, 2022Sunday, February 27, 2022Sunday, February 27, 2022Saturday, August 27, 2022Saturday, August 27, 2022
59Sunday, August 28, 202258Monday, February 28, 2022#VALUE!#VALUE!Sunday, August 28, 2022Sunday, August 28, 2022
60Saturday, September 3, 202259Tuesday, March 1, 2022#VALUE!#VALUE!Saturday, September 3, 2022Saturday, September 3, 2022
61Sunday, September 4, 202260Wednesday, March 2, 2022#VALUE!#VALUE!Sunday, September 4, 2022Sunday, September 4, 2022
62Saturday, September 10, 202261Thursday, March 3, 2022#VALUE!#VALUE!Saturday, September 10, 2022Saturday, September 10, 2022
63Sunday, September 11, 202262Friday, March 4, 2022#VALUE!#VALUE!Sunday, September 11, 2022Sunday, September 11, 2022
64Sunday, September 18, 202263Saturday, March 5, 2022Saturday, March 5, 2022Tuesday, March 1, 2022Saturday, March 5, 2022Saturday, March 5, 2022Sunday, September 18, 2022Sunday, September 18, 2022
65Saturday, September 24, 202264Sunday, March 6, 2022Sunday, March 6, 2022Tuesday, March 1, 2022Sunday, March 6, 2022Sunday, March 6, 2022Saturday, September 24, 2022Saturday, September 24, 2022
66Sunday, September 25, 202265Monday, March 7, 2022#VALUE!#VALUE!Sunday, September 25, 2022Sunday, September 25, 2022
67Saturday, October 1, 202266Tuesday, March 8, 2022#VALUE!#VALUE!Saturday, October 1, 2022Saturday, October 1, 2022
68Sunday, October 2, 202267Wednesday, March 9, 2022#VALUE!#VALUE!Sunday, October 2, 2022Sunday, October 2, 2022
69Saturday, October 8, 202268Thursday, March 10, 2022#VALUE!#VALUE!Saturday, October 8, 2022Saturday, October 8, 2022
70Sunday, October 9, 202269Friday, March 11, 2022#VALUE!#VALUE!Sunday, October 9, 2022Sunday, October 9, 2022
71Sunday, October 16, 202270Saturday, March 12, 2022Saturday, March 12, 2022Tuesday, March 1, 2022Saturday, March 12, 2022Saturday, March 12, 2022Sunday, October 16, 2022Sunday, October 16, 2022
72Saturday, October 22, 202271Sunday, March 13, 2022Sunday, March 13, 2022Tuesday, March 1, 2022Sunday, March 13, 2022Sunday, March 13, 2022Saturday, October 22, 2022Saturday, October 22, 2022
73Sunday, October 23, 202272Monday, March 14, 2022#VALUE!#VALUE!Sunday, October 23, 2022Sunday, October 23, 2022
74Sunday, October 30, 202273Tuesday, March 15, 2022#VALUE!#VALUE!Sunday, October 30, 2022Sunday, October 30, 2022
75Saturday, November 5, 202274Wednesday, March 16, 2022#VALUE!#VALUE!Saturday, November 5, 2022Saturday, November 5, 2022
76Sunday, November 6, 202275Thursday, March 17, 2022#VALUE!#VALUE!Sunday, November 6, 2022Sunday, November 6, 2022
77Saturday, November 12, 202276Friday, March 18, 2022#VALUE!#VALUE!Saturday, November 12, 2022Saturday, November 12, 2022
78Sunday, November 13, 202277Saturday, March 19, 2022Saturday, March 19, 2022Tuesday, March 1, 2022Sunday, November 13, 2022Sunday, November 13, 2022
79Sunday, November 20, 202278Sunday, March 20, 2022Sunday, March 20, 2022Tuesday, March 1, 2022Sunday, March 20, 2022Sunday, March 20, 2022Sunday, November 20, 2022Sunday, November 20, 2022
80Saturday, November 26, 202279Monday, March 21, 2022#VALUE!#VALUE!Saturday, November 26, 2022Saturday, November 26, 2022
81Sunday, November 27, 202280Tuesday, March 22, 2022#VALUE!#VALUE!Sunday, November 27, 2022Sunday, November 27, 2022
82Saturday, December 3, 202281Wednesday, March 23, 2022#VALUE!#VALUE!Saturday, December 3, 2022Saturday, December 3, 2022
83Sunday, December 4, 202282Thursday, March 24, 2022#VALUE!#VALUE!Sunday, December 4, 2022Sunday, December 4, 2022
84Saturday, December 10, 202283Friday, March 25, 2022#VALUE!#VALUE!Saturday, December 10, 2022Saturday, December 10, 2022
85Sunday, December 11, 202284Saturday, March 26, 2022Saturday, March 26, 2022Tuesday, March 1, 2022Saturday, March 26, 2022Saturday, March 26, 2022Sunday, December 11, 2022Sunday, December 11, 2022
86Sunday, December 18, 202285Sunday, March 27, 2022Sunday, March 27, 2022Tuesday, March 1, 2022Sunday, March 27, 2022Sunday, March 27, 2022Sunday, December 18, 2022Sunday, December 18, 2022
87Saturday, December 24, 202286Monday, March 28, 2022#VALUE!#VALUE!Saturday, December 24, 2022Saturday, December 24, 2022
88Sunday, December 25, 202287Tuesday, March 29, 2022#VALUE!#VALUE!Sunday, December 25, 2022Sunday, December 25, 2022
89Sunday, January 1, 202388Wednesday, March 30, 2022#VALUE!#VALUE!Sunday, January 1, 2023Sunday, January 1, 2023
Sheet15
Cell Formulas
RangeFormula
A2:A89A2=LET(a,SEQUENCE(365),b,A1+a,c,IF(WEEKDAY(b,2)<6,"",b),d,DATE(YEAR(c),MONTH(c),1),e,c-d,f,IF(WEEKDAY(c,2)<>6,c,IF(e>27,"",IF(e>13,IF(e<21,"",c),c))),g,IFERROR(f,""),h,SORT(g),INDEX(h,SEQUENCE(COUNT(h))))
B2:B366B2=SEQUENCE(365)
C2:C366C2=$A$1+B2#
D2:D366D2=IF(WEEKDAY(C2#,2)<6,"",C2#)
E2:E366E2=DATE(YEAR(D2#),MONTH(D2#),1)
F2:F366F2=IF(WEEKDAY(D2#,2)<>6,D2#,IF(D2#-E2#>27,"",IF(D2#-E2#>13,IF(D2#-E2#<21,"",D2#),D2#)))
G2:G366G2=IFERROR(F2#,"")
H2:H366H2=SORT(G2#)
I2:I89I2=INDEX(H2#,SEQUENCE(COUNT(H2#)))
Dynamic array formulas.
Click to expand...
Fluff said:
Another option
Excel Formula: 
=LET(r,NETWORKDAYS.INTL(A1,EOMONTH(A1,11),"1111100"),d,WORKDAY.INTL(A1,SEQUENCE(r,,0),"1111100"),dd,DAY(d)-1,x,IF(WEEKDAY(d)=1,d,IF(dd>27,"",IF(dd>13,IF(dd<21,"",d),d))),FILTER(x,x<>""))
Click to expand...
Can someone pls explain this to me?
 
Upvote 0
Step 1. Use the SEQUENCE Command to make an array of 365 days. So 1, 2, 3, 4, ...365. Array formulas form the basis of these kind of one-formula solutions.
2. Add that 1-365 to the start date. Now you get every date for the next year.
3 Use the WORKDAY function to look for days 6 and 7 (Saturday and Sunday) of each week. Every other day is not interesting to you.
4. Find the first day of the month for each day - so you can count out the 3 and 5th saturday from the first of the month.
5. blank out Saturdays that are greater than 13 days after the start of the month AND less than 21 days after the start of the month (the third saturday) OR any saturday that is more than 27 days after the start - the fifith saturday.
6. Sort the results array (but it is still 365 records)
7. Compress the blanks away into a smaller array. (some 88 records)
 
Upvote 0
With my formula the "1111100" specifies the weekend argument which runs from Monday to Sunday, where 1 is true (a weekend) & 0 is false (a workday).
So this part WORKDAY.INTL(A1,SEQUENCE(r,,0),"1111100") returns a list of all Saturdays & Sundays in the year
This part IF(WEEKDAY(d)=1,d,IF(dd>27,"",IF(dd>13,IF(dd<21,"",d),d))) does the same as James' formula as described above.
And the filter removes the empty cells.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
114
Fluff
Fluff
B
  • Question
countifs and remove data completed
2
Replies
10
Views
439
MuchToLearn
M
staticfluids
  • Question
Distribute Number into Several Part (Increment) Based on Other Data (Dynamic Array)
Replies
2
Views
102
staticfluids
staticfluids
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
270
Peter_SSs
Peter_SSs
A
  • Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
138
Abdo
A

Forum statistics

Threads
1,215,515
Messages
6,125,279
Members
449,220
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top