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

[James Clear]

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

 

[Tetra201]

It's giving me a 17th Jul'22 which is unfortunately 3rd Saturday.

17-Jul-2022 was just a few days ago and I clearly remember it was a Sunday

 

[Tetra201]

Late too, but is it the shortest one?

=WORKDAY.INTL(A1,1+OR(A1+4=A1-DAY(A1+1)+CHOOSE(WEEKDAY(A1-DAY(A1+1)+2),6,5,4,3,2,1,0)+{14,28}),"1111100")

@ bebo021999: Yes, your formula is the shortest one yet, but, with a starting date of 1-Jan-2022, it begins to stumble in March 2022 -- it misses the first Saturday of March (5-Mar-2022), then it misses the first Saturday of April (2-Apr-2022), first Saturday of June (4-Jun-2022), etc...

 

[Fluff]

it misses the first Saturday of March (5-Mar-2022), then it misses the first Saturday of April (2-Apr-2022), first Saturday of June (4-Jun-2022), etc...

Not only that, but it replaces the missing dates with the following Sunday.

@James Clear as bebo021999's formula does not work I have unmarked it as the solution. I would suggest that you mark one of the other suggestion as the solution.

 

[bebo021999]

Try again:

=WORKDAY.INTL(A1,1+OR(A1=A1-DAY(A1+6)+1+CHOOSE(WEEKDAY(A1-DAY(A1+6)+7),6,5,4,3,2,1,0)+{14,28}),"1111100")

 

[James Clear]

Try again:

=WORKDAY.INTL(A1,1+OR(A1=A1-DAY(A1+6)+1+CHOOSE(WEEKDAY(A1-DAY(A1+6)+7),6,5,4,3,2,1,0)+{14,28}),"1111100")

I am still trying to figure out your logic

OR(A1=A1-DAY(A1+6)+1+CHOOSE(WEEKDAY(A1-DAY(A1+6)+7),6,5,4,3,2,1,0) = This gives every time 1 then what's purpose and why Or statement in between along with Plus sign?

Why {14,28} ?? what's purpose?

Please make me understand stepwise...i am getting confused but at the same time I would want to understand each basic step

 

[James Clear]

Not only that, but it replaces the missing dates with the following Sunday.

@James Clear as bebo021999's formula does not work I have unmarked it as the solution. I would suggest that you mark one of the other suggestion as the solution.

@ Flupp

I derived my solution on my own

When K2 = 1-Jul-2022

=IF(OR(AND(TEXT(WORKDAY.INTL(K2,1,"1111100"),"ddd")="Sat",ROUNDUP(DAY(WORKDAY.INTL(K2,1,"1111100"))/7,0)=3),((AND(TEXT(WORKDAY.INTL(K2,1,"1111100"),"ddd")="Sat",ROUNDUP(DAY(WORKDAY.INTL(K2,1,"1111100"))/7,0)=5)))),WORKDAY.INTL(K2,1,"1111110"),WORKDAY.INTL(K2,1,"1111100"))

 

[bebo021999]

OR(A1=A1-DAY(A1+6)+1+CHOOSE(WEEKDAY(A1-DAY(A1+6)+7),6,5,4,3,2,1,0) = This gives every time 1 then what's purpose and why Or statement in between along with Plus sign?
Why {14,28} ?? what's purpose?
Please make me understand stepwise...i am getting confused but at the same time I would want to understand each basic step

All I tried is to find 1st Saturday in month, then +14 or 28 to get the 3rd and 5th Sat of that month
In which:
1st Sat =CHOOSE(WEEKDAY(A1+6-DAY(A1+6)+1),6,5,4,3,2,1,0)
then 3rd or 5th Sat:
=CHOOSE(WEEKDAY(A1+6-DAY(A1+6)+1),6,5,4,3,2,1,0)+{14,28})

Then if date fall into 3rd or 5th Sat, then +1, else +0
=1+OR(A1+1=A1+1-DAY(A1+6)+1+CHOOSE(WEEKDAY(A1-DAY(A1+6)+7),6,5,4,3,2,1,0)+{14,28})
=1+OR(A1+6=A1+6-DAY(A1+6)+1+CHOOSE(WEEKDAY(A1-DAY(A1+6)+7),6,5,4,3,2,1,0)+{14,28})
=1 (or 2)

General formula:
=WORKDAY.INTL(A1,1 (or 2) ,"1111100")
in which:
=WORKDAY.INTL(A1,1 ,"1111100"): if A1 fall into NOT 3rd and 5th Sat
=WORKDAY.INTL(A1,2 ,"1111100"): if A1 fall into 3rd and 5th Sat

Hope it is clear for you now.

 

[James Clear]

All I tried is to find 1st Saturday in month, then +14 or 28 to get the 3rd and 5th Sat of that month
In which:
1st Sat =CHOOSE(WEEKDAY(A1+6-DAY(A1+6)+1),6,5,4,3,2,1,0)
then 3rd or 5th Sat:
=CHOOSE(WEEKDAY(A1+6-DAY(A1+6)+1),6,5,4,3,2,1,0)+{14,28})

Then if date fall into 3rd or 5th Sat, then +1, else +0
=1+OR(A1+1=A1+1-DAY(A1+6)+1+CHOOSE(WEEKDAY(A1-DAY(A1+6)+7),6,5,4,3,2,1,0)+{14,28})
=1+OR(A1+6=A1+6-DAY(A1+6)+1+CHOOSE(WEEKDAY(A1-DAY(A1+6)+7),6,5,4,3,2,1,0)+{14,28})
=1 (or 2)

General formula:
=WORKDAY.INTL(A1,1 (or 2) ,"1111100")
in which:
=WORKDAY.INTL(A1,1 ,"1111100"): if A1 fall into NOT 3rd and 5th Sat
=WORKDAY.INTL(A1,2 ,"1111100"): if A1 fall into 3rd and 5th Sat

Hope it is clear for you now.

Shall practice at my end stepwise n understand.
Thank You for your assistance.