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

So in the first past I was using the formula

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

To calculate the first monday of each month.

We further made it interactive by having the user put the year in cell A1 and the day of the week we need the first of each month of in cell A2 to get the first of each month by using

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

Thank you to Scott Craner for that bit of code. Now what we are looking to do is take one final step further. So rather than putting a year in cell A1, how could it be done that you put in say todays date (or any date for that matter) into cell A1, then the day into cell A2 and have it calculate out the next 12 first Monday's or Tuesday's or whatever day is in cell A2 for the next 12 months?

CodePudding user response：

Simplifying Scott's formula a bit, you can try:

=LET(dt, DATE(YEAR(A1),SEQUENCE(12,,MONTH(A1) 1),8),
wkdy, MATCH(LEFT(A2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0),
dt-(WEEKDAY(dt-wkdy)))

CodePudding user response：

Edit (there was a fault in my previous post, so I tweaked Scott's version):

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