Formula to find the first Wednesday between two dates in Google sheet-CodePudding

I am looking for a Google Sheet formula to find the first Wednesday between two dates. The dates are for example 05-01-2023 and 11-01-2023, the first Wednesday is the 11th... example 05-01-2022 and 11-01-2022, the first Wednesday is the 5th...

So far my idea was to create a sequence of days between the two dates, e.g.

=sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5))

and then use this sequence as followed

=weekday(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)))=4

Where 4 is the day of the week...

Then put it all in a filter like

=filter(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)),weekday(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)))=4,1,1)

However I am getting an error: FILTER has mismatched range sizes. Expected row count: 7, column count: 1. Actual row count: 1, column count: 1.

if it all worked I was thinking of having a formula like:

=to_date(index(filter(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)),weekday(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)))=4,1,1)))

Anybody can help me out?

CodePudding user response：

This is what I intended

=A2 mod(2-weekday(A2,3),7)

BTW your formula also works with a slight tweak

=filter(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)),weekday(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)))=4)

=to_date(filter(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)),weekday(sequence(days(date(2023,1,11),date(2023,1,5)) 1,1,date(2023,1,5)))=4))