Rounding Daily Totals on Time Sheets Without Changing Employee Entries

CathieGG

New Member
Joined
May 13, 2022
Messages
11
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
I have looked everywhere, but am coming up empty.

I work at a small business that does not require any overtime from the employees. I tried using the code below, but 2 employees ended up with overtime on their calculation. 40.15 and 40.2 respectively.

=IFERROR(IF(COUNT(TimeSheet[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Lunch End]]+[@[Lunch Start]]-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)

1652456799989.png


How can I get the the total hours per day to round the Sign-In, Lunch-Out, Lunch-In, and Sign-Out to round to the nearest five minutes before totaling. But we don't want the actual times in and out to change for employees. So for example all of the Time-Ins on this sheet should be 8:00 am, and Time-Outs would be 4:30 pm for total calculation purchases. The total hours column calculations are locked. If need be, I can add a hidden column(s).

Thanks in advance.

Cathie
 
What if I rounded the regular daily hours first. Then round the total daily hours with another formula?
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
There might always be strangeness with rounding. Eventually people will figure how to game it. Here is something:
MrExcelPlayground8-1.xlsx
ABCDEFGHIJKLM
1DayDateInoutinoutregularvacasickpersonalothertotalRounded to the nearest quarter-hour
2Monday5/2/20227:2712:0012:3016:058.138.178.25
3Tuesday5/3/20227:5912:2813:0016:318.008.008.00
4Wednesday5/4/20227:5811:3612:0616:308.038.008.00
5Thursday5/5/20227:5812:2212:5316:318.037.928.00
6Friday5/6/20227:5711:2411:5516:308.038.088.00
7Saturday5/7/20220.000.000.00
8Sunday5/8/20220.000.000.00
940.2340.17
Sheet24
Cell Formulas
RangeFormula
M2:M8M2=MROUND(L2,0.25)
G2:G8G2=(F2-C2-(E2-D2))*24
G9,L9G9=SUM(G2:G8)
L2:L8L2=(MROUND(F2,5/60/24)-MROUND(C2,5/60/24)-(MROUND(E2,5/60/24)-MROUND(D2,5/60/24)))*24
 
Upvote 0
If there is no established policy with regard to these matters, then I wouldn't round at all...just compute actual time "in". Then at the end of each day, if the total time "in" is within x minutes (perhaps 3 or 4) either way from 8 hours, then it automatically gets coded as 8 hours exactly. You could always then sum up the daily variances for the entire week (i.e., total actual "in" times - 40 hrs) to see if anyone runs slightly negative consistently, suggesting they are taking some advantage of the time recording approach.
 
Upvote 0
If there is no established policy with regard to these matters, then I wouldn't round at all...just compute actual time "in". Then at the end of each day, if the total time "in" is within x minutes (perhaps 3 or 4) either way from 8 hours, then it automatically gets coded as 8 hours exactly. You could always then sum up the daily variances for the entire week (i.e., total actual "in" times - 40 hrs) to see if anyone runs slightly negative consistently, suggesting they are taking some advantage of the time recording approach.
Hi KRice - What type of formula would I used for that. Sounds much simpler. Thanks
 
Upvote 0
Going back to James earlier post, the formula could be modified to check that the daily time falls within certain limits established by a tolerance, like this. If you have Excel 365 on the machine where this processing is done, the M column formula is easier to follow (and easier to set the tolerance (tol, set here to 4 minutes). If the daily time falls outside the low/high limits established by 8 hrs +/- tol, then the actual time is reported back.
MrExcel_20220515.xlsx
ABCDEFGHIJKLM
1DayDateInoutinoutregularvacasickpersonalothertotalregular
2Monday5/2/20227:58 AM12:16 PM12:47 PM4:32 PM8.0008.000
3Tuesday5/3/20227:59 AM12:28 PM1:00 PM4:31 PM8.0008.000
4Wednesday5/4/20227:58 AM11:36 AM12:06 PM4:30 PM8.0008.000
5Thursday5/5/20227:58 AM12:22 PM12:53 PM4:31 PM8.0008.000
6Friday5/6/20227:57 AM11:24 AM11:55 AM4:30 PM8.0008.000
7Saturday5/7/20220.0000.000
8Sunday5/8/20220.0000.000
940.0040.00
Sheet12
Cell Formulas
RangeFormula
G2:G8G2=IF(AND((F2-C2-(E2-D2))*24>=(8-4/24),(F2-C2-(E2-D2))*24<=(8+4/24)),8,(F2-C2-(E2-D2))*24)
G9,M9G9=SUM(G2:G8)
M2:M8M2=LET(tol,4,act,(F2-C2-(E2-D2))*24,IF(AND(act>=(8-tol/24),act<=(8+tol/24)),8,act))
 
Upvote 0
If Excel 365 will be used, I would consider the orange column N approach...same idea as I described above, but I shortened the formula somewhat by using the absolute value of the difference between the actual daily time "in" and 8 hours. You can adjust the tolerance in one place near the beginning of the formula (change 4 minutes to whatever). Then if you are interested in compiling the daily variances, you could log actual time (shown in column O) and the daily variance (actual - 8), so negative variances mean the day was cut somewhat short. You would expect some positive and negative variances around 8 hours, but they should largely offset each other. If someone consistently generates negative variances, that would suggest clockwatching and gaming the system. This idea about maintaining full precision (at least of significant digits) for the intermediate calculations is a generally accepted practice, as rounding intermediate results leads to the accumulation of roundoff errors. In this case, you would be applying not necessarily a rounding rule, but a decision rule that says that if the time "in" is reasonably close to 8 hours, then count it as 8 hours, with the understanding that a few minutes short isn't a problem, and a few minutes long won't be compensated with overtime. The question then becomes what happens with larger variances should someone be 5 or more minutes shy of 8 hours...or 5 or more minutes longer than 8 hours. Are there penalties/overtime in those cases?
MrExcel_20220515.xlsx
ABCDEFGMNOP
1DayDateInoutinoutregularregularregularactual for comparisondaily variance
2Monday5/2/20227:58 AM12:16 PM12:47 PM4:32 PM8.0008.0008.0008.0500.050
3Tuesday5/3/20227:59 AM12:28 PM1:00 PM4:31 PM8.0008.0008.0008.0000.000
4Wednesday5/4/20227:58 AM11:36 AM12:06 PM4:30 PM8.0008.0008.0008.0330.033
5Thursday5/5/20227:58 AM12:22 PM12:53 PM4:31 PM8.0008.0008.0008.0330.033
6Friday5/6/20227:57 AM11:24 AM11:55 AM4:30 PM8.0008.0008.0008.0330.033
7Saturday5/7/20220.0000.0000.0000.000 
8Sunday5/8/20220.0000.0000.0000.000 
940.0040.0040.000.150
Sheet12
Cell Formulas
RangeFormula
M2:M8M2=LET(tol,4,act,(F2-C2-(E2-D2))*24,IF(AND(act>=(8-tol/24),act<=(8+tol/24)),8,act))
N2:N8N2=LET(tol,4,act,($F2+$D2-$C2-$E2)*24,IF(ABS(act-8)<=(tol/24),8,act))
O2:O8O2=($F2+$D2-$C2-$E2)*24
G2:G8G2=IF(AND((F2-C2-(E2-D2))*24>=(8-4/24),(F2-C2-(E2-D2))*24<=(8+4/24)),8,(F2-C2-(E2-D2))*24)
G9,P9,M9:N9G9=SUM(G2:G8)
P2:P8P2=IF(O2<>0,O2-8,"")
 
Upvote 0
Hi K - Love your idea. Working on only the regular hours column (7 minutes before or after), what did I change that goofed it up?

Time Sheet Testing.xlsx
ABCDEFGHIJKL
1
2
3
4
5
6
7
8
9Weekly Time Sheet
10
115/7/22
12Employee Name: Dept: Pay Period:
13
14Date(s)Time InLunch StartLunch EndTime OutRegularVacationSickPersonalOtherTotal
15Monday5/2/227:24 AM12:30 PM1:00 PM3:56 AM#NAME?0.00
16Tuesday5/3/22#NAME?0.00
17Wednesday5/4/22#NAME?0.00
18Thursday5/5/22#NAME?0.00
19Friday5/6/22#NAME?0.00
20Saturday5/7/22#NAME?0.00
21Sunday5/8/22#NAME?0.00
22TOTALS#NAME?00000.00
23
24OTHER HOURS DETAIL
25FLFlex Time
26HHoliday
27Employee SignatureDateFHFloating Holiday
28JDJury Duty
29BRBereavement Leave
30LOALeave of Absence
31Supervisor SignatureDate
32
33
34
Sheet1
Cell Formulas
RangeFormula
K11K11=B20
B16:B21B16=B15+1
G15:G21G15=LET(tol,7,act,(F15-C15-(E15-D15))*24,IF(AND(act>=(8-tol/24),act<=(8+tol/24)),8,act))
L15:L21L15=SUM(H15:K15)
G22:L22G22=SUM(G15:G21)
Cells with Data Validation
CellAllowCriteria
C14Any value
D14Any value
E14Any value
F14Any value
B14Any value
B15:B21Any value
C20:F21Any value
G14Any value
G15:G21Any value
 
Last edited:
Upvote 0
The NAME error gives a clue. Are you using Excel 365?
 
Upvote 0
The NAME error gives a clue. Are you using Excel 365?
Unfortunately no. Our office is still back on Excel 2010. I do have access to 365, should I go there, and then bring it back? Is that even possible?
 
Upvote 0
No worries. The LET function used to shorten the formula and assign local names like tol and act to the tolerance and the actual time "in" is available in 365, but not earlier version. Instead, the slightly longer version of the formula is:
Excel Formula:
=IF(ABS(24*($F15+$D15-$C15-$E15)-8)<=(7/24),8,24*($F15+$D15-$C15-$E15)
pasted into your row 15 daily total of regular hours cell. To adjust the tolerance, you will have to seek out the 7 and adjust if necessary.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top