Forum Discussion

Cambosity100's avatar
Cambosity100
Brass Contributor
Aug 03, 2021
Solved

Simpler Roster Conflict

Hi all. I have had help with something similar to this in December last year. This is simpler. I think. Just need a formulae for conditional formatting so that we don't roster people to conflicting s...
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Aug 04, 2021

    Cambosity100 

    Hopefully this is better; I rather missed the point on the conditional formatting.  This time I defined a Boolean 'Conflict?', 

    = IF(ISTEXT(currentAssigned),
     COUNTIFS(
       otherAssigned, currentAssigned,
       otherStart,  "<"&currentFinish,
       otherFinish, ">"&currentStart
       ) > 1)

    based upon relative references, to control the format.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 04, 2021

Cambosity100 

Hopefully this is better; I rather missed the point on the conditional formatting.  This time I defined a Boolean 'Conflict?', 

= IF(ISTEXT(currentAssigned),
     COUNTIFS(
       otherAssigned, currentAssigned,
       otherStart,  "<"&currentFinish,
       otherFinish, ">"&currentStart
       ) > 1)

based upon relative references, to control the format.

 

Cambosity100's avatar
Cambosity100
Brass Contributor
Aug 04, 2021

PeterBartholomew1 Peter that's absolutely perfect ! Just some questions about importing it into the real world. I can't even find where the formulas are LOL. With Hours Tallied am I able to drag the Formula? As there are about 40 staff and growing. And do I just extend the range of the Conditional Formatting to accommodate 60 +Clients and growing ? Also could you find a way to display (maybe on a new sheet ?) the quietest an busiest times of each day, say hour by hour so that we can plan times for staff meetings ? I think I can do it but I would love to see your solution ! I hope I am not asking too much 🙂 

Resources