Forum Discussion

CDoescher4's avatar
CDoescher4
Copper Contributor
Apr 08, 2026

Creating weekly calendar template that has overlapping positions

Hi, 

I have a software for individuals to sign up for shifts, but it is visually hard to see where there are open areas that need to be filled. I need help creating a calendar from this software download that I can update each week. It would have to be much like an hourly scheduler view to easily see when someone is coming on and off shift. But also laid out from Sun-Saturday. There is 3-4 "slots" for each day. It would be great if they could be color coded as well. Slot 1 needs to be anyone that is "AEMT" in the position column, Slot 2 can be anyone listed as "EMT" or "EMR" (caveat to this would be that slot 2 could also be AEMT if there is already an AEMT in slot 1 for the same time slot. Slot 3 is for driver or ride along (or the other 3 positions if there is one listed in the other slots), and possibly 4th slot would just be overflow in case there were 4 people signed up which does not happen very often. Is there anyone who can help me with designing this? Below is what the software generates when converting to excel

 

 

community
excel
Formulas and Functions

3 Replies

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Apr 14, 2026

    I sent you an answer via private mail because it keeps deleting my posts here.  Please let me know if you get it and the workbook.

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Apr 14, 2026

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