I am attempting to automatically count the number of staff we have per hour over a 24 hour period. We have staff starting and finishing at different times throughout the day/night and currently count it manually. I have put together the attached worksheet which seems to work for majority of shifts but comes in to trouble at certain times... I believe this is because we can't continuously loop the times.
We enter the staff members name, start and finish times in one of the designated boxes in the 'Daily staffing' worksheet and then want the staff per hour count to automatically update in rows 6 and 8 in the same worksheet - these cells B6-O6 and B9-O9 update correctly for majority of shifts entered but fails if you enter certain times.. for example a 1900-0700 shift in one of the start/finish columns on the worksheet displays incorrect data in the cells of row 6 and 8.
For example if we enter a staff members name in G14 with a start time of 0700 in I14 and a finish time of 1500 in J14 row 6 updates correctly and shows a total of 1 across B6-J6 correlating with the shift time 0700-1500.
BUT if we enter a shift time of 1900-0700 in cells I14 and J14 respectively the count in row 6 and 8 does not correlate correctly.
I can see issues in the code but I just can't find a solution if anyone has any ideas please?
Secretly posted here, too: https://www.mrexcel.com/board/thread...eriod.1198964/