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 03, 2021

Cambosity100 

I was rather hoping someone would have posted a solution to this question before I posted 'work-in-progress' which hopefully will lead to a completely dynamic solution once I have the new Lambda helper functions.  As it is, I still have some relative referencing.

 

The underlying data is essentially 3 arrays (rostered team, start time and finish time) combined by interlacing rows and columns.  Although it is easy to extract the constituent arrays, aggregations (normally requiring COUNTIFS) are tricky when one is working with arrays rather than range references.  Even something as simply as summing the rows is not easy when working with arrays (MMULT could be used) but, as a temporary fix, I have fallen back on relative referencing.

 

The conditional formatting seems to work.  Being antiquated functionality, there is no alternative to single-cell, relative referencing.

 

Conditional format

= IF( ISTEXT(currentCell),
  COUNTIFS(currentColumn,currentCell) > 1)
  • Cambosity100's avatar
    Cambosity100
    Brass Contributor
    Aug 04, 2021

    PeterBartholomew1 Also Peter, the hours tallied weekly formula is brilliant. A million times better that embedded IF's 🙂 Now I need to research LET .... Thanks again 🙂

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

    PeterBartholomew1 Hi Peter. Thank you I really appreciate your interest in this problem. I have looked at your Roster for further work and in its current state it appears to not be picking up time conflicts but name duplicates. For example on Thursday Staff 3 could easily service Client 1 and Client 8 as there is a 5 hr gap between the shifts. Also, for example, options for conflict resolution on Tuesday I would probably either select another Staff or get permission to "slide" the Client 2 shift forward one hour making it 1000 to 1600 hence no longer conflicting with Client 4. Client 11 would need another Staff member. With this in place the red should disappear. I hope this makes sense. So in short in this industry it is common if not the norm for a Staff member to do multiple shifts per day. Its just making sure the times don't conflict. 🙂 Once again my thanks for your help...

Resources