Roster more effectively with Excel Ep2: Sleep Days after Night Shifts

For those of us writing rosters with 24-hour coverage, there is the necessity to account for sleep days for those that are transitioning from night shifts to day shifts. Sometimes in the deep roster writing state, we can lose track of these things in the struggle to fill every shift. Let's set up some Excel formulas to check this for us instead of seeing it ourselves much later.

The setup

We can use the same setup as the last time in this series. The same 6 'people' (pets), and one week. Some of the Day and Night Shifts are denoted by their shift codes. In this scenario, our policy is to have two days off after the night shift before the next day. Lastly, we are marking our night shifts on the day they start, even though they would finish the next day. Our rule break is always from Wednesday to Friday, and we will cover different complications and how to have one comprehensive rule for them.

The Manual Method

This tried-and-true method is to simply mark sleep days on your roster. In this example, we have used '~'. It's quick and easy to get started but does add to the work of rostering.

Highlighting not enough sleep days

When we only have one night shift, things are quite manageable. We will be using conditional formatting to highlight every night shift that doesn't have enough sleep days afterwards. Let's add a rule to the entire area where shifts are assigned, but instead of one of the standard options, we'll have to write our own formula.

The formula in this example is: =IF(C2="N",COUNTA(D2:E2)>0,FALSE)

Let's break down how this works and how we can adapt it for different rosters.

The first part, '=IF(C2= "N"' tells Excel that we are only running this on days where we have put in an "N" shift. This can be adapted by changing the part in "quotation marks" to whatever your night shift is called. The C2 reference should be on the first cell of the area in which we are checking. The ",FALSE)" at the end closes our IF statement because we aren't interested in checking when it's not a night shift.

The COUNTA(D2:E2)>0 part is how we check that we haven't put in any shifts after our night shift or, in Excel terms, that the two cells after our night shift are empty. To adjust this to suit more or less sleep days, the range it checks needs to be enlarged or shrunk.

Ignore false positives

The problem with the check we have set up, as you can see from the picture, is that it will throw us false positives. Ignoring false positives is easy if our night shifts have to be continuous; harder if there can be a day (night) off between them. We're using the same method but adapting our formula to be a bit smarter.

For continuous:

=IF(AND(NOT(D2=“N”),C2="N"),COUNTA(D2:E2)>0,FALSE)

The bold section has been added to our previous formula. We have added a condition to the day that we will not be checking if sleep days are given if the day after this shift is also a night shift. That way, the formula only checks on the last night shift of any given series of shifts.

For not continuous:

=IF(C2="N",SUM(COUNTIF(D2:E2,INDIRECT(“Table1[Day Shifts]”)))>0,FALSE)

If we can have a day off between night shifts, it gets considerably more complicated. First, we have to prepare a list of non-night shifts. I've made it into a table so I can more easily remember what it is later on. We've replaced what we're counting with a bit more specificity. Where previously we were counting empty days, now we are counting all our different not night shifts and adding them together with the SUM function. As before, we should see 0, so if it's greater than 0, we haven't been given enough sleep days. The last thing to note is we need to throw an INDIRECT function in there to make conditional formatting work.

Multiple Night Shift Types

=IF(ISNUMBER(MATCH(C2,INDIRECT("Table1[Night Shifts]"),0)),SUM(COUNTIF(D2:E2,INDIRECT("Table1[Day Shifts]")))>0,FALSE)

Since we've accounted for many different types of not-night shifts, let's also add that in for night shifts as well. As you can see, I've expanded my table to have a night shifts category, and as before, we're only checking if the day we're on is one of our night shifts before we start counting if there have been enough sleep days. This time we've done it a slightly different way, with a MATCH function to tell us if the day we're on is on our list of night shifts.

Highlight all the days

I find that highlighting just the night shift isn't enough information, especially since I often put in other checks and may be highlighting a cell for many reasons. For more clarity, I like to highlight the night shift and all mandated sleep days when they've been forgotten.

It's a little tedious, but we can simply add multiple conditional formatting rules and offset the starting point of each one. With more mandatory sleep days, we'll just copy and modify our formula more times.

Conclusion

This has been a thorough run down on how to have automatic highlighting for missing sleep days in Excel.

If you'd like to implement these but are short on time or just don't want to put in the not-insignificant effort, try out our free manual scheduling platform. Alternatively check out our free 6-week Excel roster.