How to substract for lunch on =MOD function

[jcc31]

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.

Thanks in Advance

 

[Pete_UK]

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*(B4>0) - 1/48*(D4>0) - 1/48*(F4>0) - 1/48*(H4>0) - 1/48*(J4>0) -
1/48*(L4>0) - 1/48*(N4>0)

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

 

[JE McGimpsey]

One way:

=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) - FLOOR(COUNT(B4:O4)/2,1)*1/48

 

[Harlan Grove]

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)

 

[Harlan Grove]

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.