Time calculations

[joe z]

I have a spreadsheet that calculates employees time on their projects. Column
A is their start time and column B is their stop time. The formula calculates
the time taken, but I have to manually deduct their breaks. With 9 shift
zones, how can I fit in a logical formula that recognizes that when a break
occurs between the start and stop time to deduct either the 15 minutes or 30
minutes if on lunch?

 

[Biff]

Hi!

You need 2 more cells for out lunch and in lunch: (or break)

A2 = start
B2 = out lunch
C2 = in lunch
D2 = end

=B2-A2+D2-C2

Or, if you have standard break times (length) and lunch times (length) you
could use a Yes/No to denote if lunch or break times need to be deducted:

A2 = start
B2 = end
C2 = break taken = Yes or No (15m break)
D2 = lunch taken = Yes or No (30m lunch)

=B2-A2-(C2="Yes")*15/1440-(D2="Yes")*30/1440

Biff