hours breakdown, Ordinary, O/time 1.5 and O/Time 2.0

Hi Guys and girls!

Hoping you maybe able to assist me in a formula breakdown.
I have asked a few work colleges and we can not seem to find the answer.

What I am needing is a formula to help shift the hours in the correct columns.

Attached for example.

As you can see I need a formula/s which will work out the ordinary, O/T 1.5 and O/T 2 by only putting in information to start and finish time. I am not sure if this is achievable or maybe i am asking excel to do a little too much?
Any help is greatly appreciated, I am a bit of a "noob" at excel!.

How do you differentiate what goes under O/1.5 and O/2? I suspect it's to do with the hour of the day.

Yes, so we have a break down...
first 8hrs Monday to Friday between 6am and 6pm are classed as "Ordinary" then first 2 hours overtime are classed as "1.5hrs O/Time" then every hour after that is double "2hrs O/Time"
Then the harder part is...
6pm to 6am monday to friday first 2 hours are "1.5hrs O/Time" then every hour after that in "2hrs O/Time"
Then..... there is the weekend....
Saturday first 2 hours are at "1.5hrs O/Time" then "2hrs O/Time" Every hour after that.
And...
Sunday Double all day "2hrs O/Time"

Ive attached the original spread sheet that we created, but I want to simplify it as much as possible, and take away human error..

Perhaps something like this?

This is based off the first example you provided, but I've added in dates, and furthermore, I've changed the way the times are put in.

Basically, 1.00PM is typed into the cell as 13:00 such that Excel recognises it as a number rather than a text.

Quekbc! You are a LEGEND! That there is some mean formula writting!. I will have a play with it tomorrow, if i have any questions i will let you know. Thank you for all your help!.

All in a day's work stax290.

I'll happily answer any follow-up questions you may have. Since you're in Melbourne, have a cup of coffee for me will you.

Well it's almost that time of day so might cheers you to a wine when I get home 😄

Quekbc...

I have a question, When the staff work "Night Shift" how can I calculate the hours to the correct O/Time columns?. So again first two hours are at "1.5hrs O/Time" then the rest at "2hrs O/Time"... Can you help?

Good morning Stax290.

Forgotten about that bit between 6am and 6pm. Made some changes (OT 1.5 and OT2 remains the same). Please see attached.

Formula:

Please Login or Register  to view this content.

Also, be careful when you are inserting the dates. They were showing #VALUES in your attached file because the typed in date was recognised as a text by Excel. I'd recommend just type in the date 31/12/2015 and not Wednesday, 31 December 2015 as it may result in human error (i.e. typed in wrongly).

Hello, need some help
I have total hours worked and i need to breakdown in ordinary, time and half and double time when meet conditions
after first 7.25 hours become time and half for the first 2 hours, then become double time (weekdays)
Saturday first 2 hours time and half then double time for remaining
Sunday all double time
Capture.PNG

Good morning,
This excel template is awesome, but we would require a deduction for lunch breaks.
Would anyone be able to supply the necessary adjustments to this timesheet template??
It would lessen our payroll process considerably.
Thank you
V