I'd like a formula in F7 that would transpose an array of dates across row 7 based on the current week in H4 and the weekday in C5 - so that I can print the workbook each week without manually writing/typing the dates on each tab. This is for an afterschool program.
'DESIRED OUTPUT' shows an example. Tabs 2-4 are blank. 'HELPER DAYS' has previous helper data that I was tinkering with.
The biggest challenge I'm anticipating: some months may have more than 4 Tuesdays etc, which would spill over the formatted columns I currently have built.
Any guidance would be much appreciated!!!
You can use the WORKDAY.INTL function.
=ARRAYFORMULA(LET(
wd, SWITCH(C5,
"Monday", 1,
"Tuesday", 2,
"Wednesday", 3,
"Thursday", 4,
"Friday", 5,
"Saturday", 6,
"Sunday", 7
),
d, WORKDAY.INTL(
EOMONTH(H4, -1),
{1, 2, 3, 4, 5},
SUBSTITUTE("1111111", 1, 0, wd)
),
TOROW(IFNA(VSTACK(d,,)), 2, 1)
))