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

Harlan Grove wrote...
....
=MMULT(--B4:O4,{-1;1;-1;1;-1;1;-1;1;-1;1;-1;1;-1;1})

....

I forgot that you used MOD to catch time-in and time-out on different
sides of midnight. That would change the formula to

=SUM(MOD(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}),1))

and would change the lunchtime formula to

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

These may appear to add typing, but you could create a define name like
X (being cryptic on purpose) referring to

=({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})

and use array formulas like

=SUM(MOD(MMULT(--B4:O4,X),1))

and

=SUM(MOD(MMULT(--B4:O4,X),1)-(MMULT(--B4:O4,X)<0)/48)

which are as short as possible.