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

jcc31 wrote...
I have this function to calculate a weekly total hours for a person.
This is for a 7 day week schedule.

=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)

How can I substract 30 minutes for lunch per day that this person
works.. Keep in mind that this person will sometimes work 3 days of the
week and sometimes he/she will work 5 days of the week. The days of the
week will vary.


How are you sure they always take lunch or always take only 30 minutes
for lunch? If the difference between time-in and time-out is 3 hours or
less, would they have taken lunch? Better question: would you be in
compliance with labor laws in your location if you arbitrarily assume
they always take lunch no matter how few hours they may work? Where I
am, the fines the state would impose for such assumptions dwarf the
possible costs of providing employees paid lunch breaks. Before giving
another answer, I'd point out you'd be better off having these people
clock out at the beginning of their lunch breaks and clock back in at
the end of them, then calc hours worked as time worked from arrival to
lunch + time worked from lunch to departure.

You see to have time-in entries in cols B, D, F, etc., and time-out
entries in cols C, E, G, etc. You could get tricky and rewrite the
formula above as the array formula

=MMULT(--B4:O4,{-1;1;-1;1;-1;1;-1;1;-1;1;-1;1;-1;1})

The -- for the 1st argument is needed to convert blank cells into zeros
because MMULT is pathologically stupid about blank cells. Following the
matrix multiplication approach, the array formula

=SUM((MMULT(--B4:O4,({1;1;2;2;3;3;4;4;5;5;6;6;7;7}={1,2,3,4,5,6, 7})
*{-1;1;-1;1;-1;1;-1;1;-1;1;-1;1;-1;1})0)/48)

should give the lunch half hours for days worked (which presumably are
the days on which time-in and time-out are the same, blank). Net work
hours would then be given by the array formula

=MMULT(--B4:O4,{-1;1;-1;1;-1;1;-1;1;-1;1;-1;1;-1;1})-SUM((MMULT(--B4:O4,
({1;1;2;2;3;3;4;4;5;5;6;6;7;7}={1,2,3,4,5,6,7})*{-1;1;-1;1;-1;1;-1;1;-1;1;-1;1;-1;1})0)/48)