Error in time of day in formula

[Barrad0x]

Hi all, I need some advice on how to solve this problem please: I’m trying to find a formula that will auto sum a works timesheet where on a Saturday the overtime rates are different from 00:00 (Friday/Saturday midnight) to 12:00 (Saturday lunchtime), then change to a different rate after lunch time.

So for example; if I start work at 09:00 and finish at 16:00:

1

All I want to do is input start and finish times (A2 & B2), then have Excel auto populate the cells C2, D2,E2, F2 and G2.

If no hours worked then to show a blank cell.

I can sort out the currency formulas, but I’m getting stuck when calculating the hours in C2 & D2.

Any ideas???

Thanks in advance!

[#2 · Accepted solution]

@Barrad0x 

See the attached sample workbook.

[#3]

Yes Hans - Thats what i needed! you are amazing, thanks! And if i wanted to change the times? say rather than 00:00 to 12:00, maybe 05:00 to 07:30? whats the change in formula?

[#4]

@Barrad0x 

See the attached. If it gets more complicated than this, it would better to change the setup a bit.