Hours worked on Different Shifts - Breakdown on overtime and ordinary

**Rangedale** · 07-22-2015, 12:18 AM

Hi There

I need your help. in a desperate situation and I am really worried I cant do this on my own. Pleas help...

Ordinary hours = 6am - 6pm
O/Time Hours = 6Pm - 6 am

O/Time hours are calculated, first 2 hours is paid at a different rate so needs to move separately to a 1.5 column and the rest moves into 2 column.

So as below, my table will look like this;
A B C D E F G
1 Name Start Finish Ordinary O/T 1.5 O/T 2 Total hrs
2 Bradd Clabby 9:00am 6:00pm 8 1 0 9
3 Bradd Clabby 6:00am 8:00pm 8 2 4 14
4 Bradd Clabby 4:00pm 5:00am 2 2 9 13

As you see from the table above, I need formulas which will work out the ordinary, O/T 1.5 and O/T 2 by only putting in information to start and finish time

Basically, need the formula for Cell D2, Cell E2, Cell F2, Cell G2. I have use If or vlookup formulas and failed when someone works between day and night shift. Do you think you can help?

**Rangedale** · 07-22-2015, 12:30 AM

I think my table looks weird so I am posting it again. Thanks
A B C D E F G
1 Name Start Finish Ordinary O/T 1.5 O/T 2 Total hrs
2 Bradd Clabby 9:00am 6:00pm 8 1 0 9
3 Bradd Clabby 6:00am 8:00pm 8 2 4 14
4 Bradd Clabby 4:00pm 5:00am 2 2 9 13

**Rangedale** · 07-22-2015, 12:30 AM

Seriously ?

Not working

**FDibbins** · 07-22-2015, 12:34 AM

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.