Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to substract for lunch on =MOD function
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 -- jcc31 ------------------------------------------------------------------------ jcc31's Profile: http://www.excelforum.com/member.php...o&userid=24692 View this thread: http://www.excelforum.com/showthread...hreadid=520653 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to substract for lunch on =MOD function
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 In article , 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. Thanks in Advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |