Timesheet Calculation for Shift workers

Hoping to get some more help here today with my timesheet as I did yesterday.

As per attached spread sheet in the yellow highlighted cells, I am trying to input a formula to automatically calculate the hours between specific times for shift workers so I can easily calculate their penalty rates however I just cant seem to get it to work, the main issue being midnight.

Any help would be greatly appreciated.

Cheers

Thanks so much for your replies rcm & gak67. Very helpful and I am definitely getting somewhere now. Both calculations work with that one particular scenario but if I have different start/ finish times, then I cant get it to work quite right.

I have attached another spread sheet with your suggested calculation RCM, it did work perfectly until I put in an end time that is less than 22:00 then it throws it out (Note: I have highlighted the incorrect calcs in red). I tried adjusting it, then that scenario worked but another didn't. Any suggestions? I think I have now been looking at it too long now that it is probably something simple that I just cant put my finger on!

Any further suggestions would be greatly appreciated.

Looking forward to hearing back

A simple change to cells in D fixed the normal hours rule, and conditional formatting of the penalty columns to hide 0 or <0 values cleans the sheet up

Thanks for your reply dubcap01, however I dont think I was clear with what normal hours represent. Normal Hours is time worked between 7am to 10pm. Normal meaning no penalty rates. Total Hours is the employees total hours for that shift.

Therefore, for example, cell D2 should equal 5. 5 hours between 5pm to 10pm. Cell E2 should equal 2 (which is correct), and cell F2 should equal 1.5 (also correct). So in this instance, its the normal hours which I am having trouble with.

The three time frame examples all need to work as they are all examples of hours that an employee may work in any given week.

Hoping you may have another suggestion?
Thanks

Thanks nflsales! That works perfectly. I didnt realise when I first looked at it as the hours calcs were in time format so I just multiplied all by 24 which worked.

Thanks again, I really appreciated your help

TRy it now

Fantastic! Thanks again rcm