Time Tracking Log

Note: this is also posted on https://www.mrexcel.com/board/thread...-logs.1175498/

Looking for help with the best approach to take. I started working on a file that seemed simple, but is becoming more complex than I anticipated.

I have a team that works overnight 5PM to 130AM. There are 2 clients that they may be assigned to (Client A or Client B). After they are assigned a client, they are assigned one of two tasks to complete (Picking or Packing). Team members move from one task to another and from client to client during their shift. I need to track how much time is spent on each task, for each client. This needs to be calculated hourly. It seemed a simple log was the answer but its not turning out that way:

See attached

Each hour, on the hour, I need to be able to see how much time was spent in each task for each client
EX1: How many hours were used from 7PM-8PM PACKING HUGO
EX2: How many hours were used from 8PM-9PM PICKING MORPHE

one last piece to complicate it:
If the "in time" meets the lower boundary criteria, but the "out time" has not been entered then use upper boundary value as "out time" to calculate duration