If I was to do a formula like (c5-b5)+(e5-d5) how can I subtract 2 from this
and still work?

"Roger Govier" wrote:

Hi

To get the calculation for Monday
=MOD(C1-B,1)-(TIME(1,0,0)*COUNT(B1:C1)=2)

Using MOD() rather than just subtracting B1 from C1 will deal with any
occurrences of times running over from 1 day to the next.
Deducting TIME(1,0,0) is being multiplied by the text to ensure you have
both start and end times, so the formula does not deduct an hour for any
days not worked.
Repeat procedure for other days and sum results.
The cell containing the formula that sums your intermediate calculations
would need to use FormatCellsNumberCustom[h]:mm in order to sum
past 24 hours.

--
Regards

Roger Govier


"RJ Swain" <RJ wrote in message
...
I am putting together a simple excel spreadsheet to calculate hourly
schedules for my employees. What I would like it to do is calculate
the hours
of the week and subtract 1 hour per day for lunch hours. So it is
setup as
follows

A1 - Name
B1 - Start
C1 - Finish (Monday) (-1 hour lunch)
D1 - Start
E1 - Finish (Tuesday) (-1 hour lunch)
etc..
N1 - Total hours for the week