Shift Schedule Matrix 14 day pay period by 24 hours

[DE_Tx]

I am trying to write a formula that would provide the results shown in the attached file. I can handle the team members that do not work overnight. I am having issues with those that do work overnight and then have Off days.

I've tried searching for various scenarios with no luck.
If someone can point me to a thread that would be helpful or perhaps take a stab at a formula, that would be greatly appreciated.

Columns A:E are coming from a legacy system.

DET

 

[Hui]

Please see attached



I used some helper cells in Columns AH:AN to calculate the over night shifts

 

[DE_Tx]

Thank you, Hui. I was considering using helper columns but for some forgotten reason, went another direction - that didn't work.

I've decided to take this to 30 minute increments and I used your method to get there. I'm having trouble with schedules where Off days are not consecutive.
I've attached an example.

There are enough instances where fixing these manually would be burdensome.
Any ideas you may have to address the floating days off would be greatly appreciated.

DET

 

[Hui]

Copy M8:BW8 down
It looks ok to me