Formula based on first monday or tuesday etc of the week-CodePudding

Is there a way to put a year into a cell (say A1), then the day of the week you want (say Monday in cell A2) and enter into the 12 cells below it the first monday of each month (or first tuesday, etc, based on whats in cell A2)?

I have been able to figure out how to do it with 1 day but I would like it to be interactive.

=DATE(2022,{1;2;3;4;5;6;7;8;9;10;11;12},7-WEEKDAY(DATE(2022,{1;2;3;4;5;6;7;8;9;10;11;12},1)-5,3))

CodePudding user response：

With Office 365:

=LET(yr,A1,
    wkdy,MATCH(PROPER(A2),{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0),
    mnt,SEQUENCE(12),
    DATE(yr,mnt,1) MOD(8-WEEKDAY(DATE(yr,mnt,1),10 wkdy),7))

CodePudding user response：

You where pretty close with your formula

=DATE(A1,{1;2;3;4;5;6;7;8;9;10;11;12},7)-WEEKDAY(DATE(A1,{1;2;3;4;5;6;7;8;9;10;11;12},7),A2)

Where A1 = year string A2 = day of the week as number (1 = Saturday, 2 = Sunday, etc.)