Excel 2007 : Time Calculation

[AlanWade]

Hi,
In my time report I have Start Time, Finish Time, Lunch Time, Total and Overtime Hours. Because I work nights, I am using the MOD command to calculate the worked hours which boils down to Start Time - Finish Time - Lunch Time.
The problem I have is that we are paid extra time from 8pm to 6 am. Is there an easy way to get the spreadsheet to calculate the hours between the Start Time minus the Lunch Time upto 06:00?
Everything I have tried just dosnt work.
I have attached a copy of of my Time Report so you can see what I am talking about.
Thanks in advance
Alan

[rwgrietveld]

=(c11<b11)+c11-b11-d11

Is is my assumption that (End-Start) > Lunch. At least, that is for most workers. LOL

[AlanWade]

Thanks for the prompt reply but that formula calculates up to the finish time. I need it to calculate only up to 06am.
Regards
Alan

[daddylonglegs]

It depends what type of shifts you might have. For your examples this will be sufficient

=MOD(MIN(C11,0.25)-B11,1)-D11

but what should be the result for a day shift like 09:00 to 17:00?

[AlanWade]

Thanks for your reply Dadylonglegs. I only work nights so the day time calculations wont be a problem. For some reason, no matter what I enter for a start time, it gives a result of 6 hours. e.g when I type in 01:30 as a start time and 00:30 for lunch the extra payment should be 4 hours but it is showing 6 hours.
Alan

[AlanWade]

Scratch that last post, I was typing in the wrong field - DUH!!
Now it works but if any of the time fields are blank or have no time entered, the extra hours is showing as 6 ???
I dont work every day so it is a problem.
I have attched a new sample so you can see what I mean.
Many Thanks
Alan

[daddylonglegs]

You can check to see whether the start time is blank, e.g.

=IF(B11="","",MOD(0.25-B11,1)-D11)

That will show a blank unless there's a start time, or for a zero change to

=IF(B11="",0,MOD(0.25-B11,1)-D11)

[AlanWade]

Thanks very much for your time and patience daddylonglegs. Everything works perfectly now.
Best Regards
Alan