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 dow...
community
excel
Formulas and Functions
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.

  • CDoescher4's avatar
    CDoescher4
    Copper Contributor
    Apr 15, 2026

    m_tarler​ Thank you for this. I am not great at excel formulas, but this seems to be sufficient enough for what the team needs to quickly visualize where there are gaps in coverage during the week. I can try to play around with coloring and hopefully I don't mess it up too much. Otherwise if you know how to quickly fix it, the AEMT would be blue, EMT/EMR pink and the driver/ridealong would be green. I appreciate all the help! 

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

      Hi CDoescher4​ ,  I'm glad this is helpful.  I have some questions on how to make the changes you are hoping for.  First off your PM and this post don't agree on the color coding:

      AEMT:  Blue / Blue
      EMT:    Pink / Pink
      EMR:    Pink / Green
      Driver:  Green / Yellow
      Ride Along:  Green / Red

      The coloring isn't hard, I added 5 conditional formatting rules to make the names be colored based on their role.

      As for how a single person/shift goes from slot 3 to slot 2 and back to slot 3, that is an unfortunate consequence of how I do the functions, BUT I also think it is a necessary side-effect because if you notice in the image below Courtney S goes from slot 3 to slot 2 and back to slot 3 AND Morgan L goes from Slot 4 to 3 and back to 4 AND you also have Arielle D that overlaps that timeframe also.  Let's pretend Arielle D was a "Driver" (and not an EMR) then if we had Courtney S going straight down column 3 and Morgan L going down slot 4 then even though there is an opening in slot 2 then Arielle D (assuming them as a "Driver") couldn't get added because slot 2 is restricted from allowing a "Driver" role.  So I already did an order precedence based on roles and then how long they are on shift that day so in theory it should lessen that since someone on shift for only 4 hours is less likely to cross the transition of someone working 12 hours (as opposed to the other way around).

      I did update the formulas because I didn't take into account "Ride Along" and when I did I also created the precedence order to be AEMT, EMT, EMR, Driver, Ride Along but if you prefer I can change that up to be AEMT, EMT/EMR, Driver/Ride Along  (e.g. the second column would list an EMR with >shift time before EMT).