View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default How to substract for lunch on =MOD function

Presumably you have start and end times in all the cells referenced in
the formula, and if the person does not work on a particular day the
start time would be empty? If so, try this:

=MOD(C4-B4,1)+MOD(E4-D4,1)+MOD(G4-F4,1)+MOD(I4-H4,1)+MOD(K4-J4,1)+MOD(M4-L4*,1)+MOD(O4-N4,1)
- 1/48*(B40) - 1/48*(D40) - 1/48*(F40) - 1/48*(H40) - 1/48*(J40) -
1/48*(L40) - 1/48*(N40)

30 mins is half an hour, or 1/48th of a day (if you are using Excel
time formats). Watch out for line wraps - all one formula.

Hope this helps.

Pete