Forum Discussion
Creating weekly calendar template that has overlapping positions
Dear CDoescher4 , it was a bit of work but I think I have something that may work for you. Here is the basic output:
so each day has 4 columns (because you said 4 doesn't happen very often but yet the data you gave appears to have 5 people signed up for the first part of the first day. It will fill in each timeslot per your rules prioritizing AEMT then EMT/EMR then Driver and will 'prioritize' people with more hours that day over those with less. I don't know what "colors" you would want and where. As you can see on Tuesday you have AF in slot 2 with slot 1 open because they are not an AEMT.
The formula is far from trivial using multiple LAMBDA functions and are locate in the upper left corner of each day (i.e. cells B4, F4, J4, ....) and the dates in row 2 are based on the starting date in D1, which for now is just grabbing the first date from the data sheet. Here is the formula in case the file doesn't come through...
=LET(data,Data!$A$2:.$H$99,times,$A$4:$A$99,date,D2,
dStart,XMATCH(date,TAKE(data,,1)),dEnd,IFERROR(XMATCH(date+1,TAKE(data,,1)),ROWS(data)+1),
in,TAKE(DROP(data,dStart),dEnd-dStart-1),
sTimes,IF(CHOOSECOLS(in,5)<date,date,TAKE(in,,1)+CHOOSECOLS(in,5)),eTimes,IF(CHOOSECOLS(in,6)>date+1,date+1,CHOOSECOLS(in,3)+CHOOSECOLS(in,6)),roles,TAKE(in,,-1),
todayData,SORTBY(HSTACK(sTimes,eTimes,TAKE(in,,-2)),eTimes-sTimes+24*(roles="AEMT")-24*(roles="driver"),-1),
insert,LAMBDA(name,role,slots,LET(place,IFERROR(XMATCH(1,HSTACK(role="AEMT",role<>"driver",1,1)*(slots="")),0),IF({1,2,3,4}=place,name,"")&slots)),
timeslot,LAMBDA(t,REDUCE({"","","",""},SEQUENCE(ROWS(todayData)),LAMBDA(p,q,IF((t+date>=INDEX(todayData,q,1))*(t+date<INDEX(todayData,q,2)),insert(INDEX(todayData,q,3),INDEX(todayData,q,4),p),p)))),
DROP(REDUCE("",times,LAMBDA(p,q,VSTACK(p,timeslot(q)))),1))