CODEWITHSUNDEEP
dategoogle-sheets
Marc Witteveen
Marc Witteveen

Reputation: 741

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

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?

Upvotes: 1

Views: 314

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34380

This is what I intended

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

enter image description here

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)

or

=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))

Upvotes: 2

Related Questions