Rounding Daily Totals on Time Sheets Without Changing Employee Entries

[CathieGG]

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)

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

 

[CathieGG]

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

 

[JamesCanale]

There might always be strangeness with rounding. Eventually people will figure how to game it. Here is something:

MrExcelPlayground8-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Day	Date	In	out	in	out	regular	vaca	sick	personal	other	total	Rounded to the nearest quarter-hour
2	Monday	5/2/2022	7:27	12:00	12:30	16:05	8.13					8.17	8.25
3	Tuesday	5/3/2022	7:59	12:28	13:00	16:31	8.00					8.00	8.00
4	Wednesday	5/4/2022	7:58	11:36	12:06	16:30	8.03					8.00	8.00
5	Thursday	5/5/2022	7:58	12:22	12:53	16:31	8.03					7.92	8.00
6	Friday	5/6/2022	7:57	11:24	11:55	16:30	8.03					8.08	8.00
7	Saturday	5/7/2022					0.00					0.00	0.00
8	Sunday	5/8/2022					0.00					0.00	0.00
9							40.23					40.17
Sheet24

Cell Formulas
Range	Formula
M2:M8	M2	=MROUND(L2,0.25)
G2:G8	G2	=(F2-C2-(E2-D2))*24
G9,L9	G9	=SUM(G2:G8)
L2:L8	L2	=(MROUND(F2,5/60/24)-MROUND(C2,5/60/24)-(MROUND(E2,5/60/24)-MROUND(D2,5/60/24)))*24

 

[KRice]

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.

 

[CathieGG]

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

 

[KRice]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Day	Date	In	out	in	out	regular	vaca	sick	personal	other	total	regular
2	Monday	5/2/2022	7:58 AM	12:16 PM	12:47 PM	4:32 PM	8.000						8.000
3	Tuesday	5/3/2022	7:59 AM	12:28 PM	1:00 PM	4:31 PM	8.000						8.000
4	Wednesday	5/4/2022	7:58 AM	11:36 AM	12:06 PM	4:30 PM	8.000						8.000
5	Thursday	5/5/2022	7:58 AM	12:22 PM	12:53 PM	4:31 PM	8.000						8.000
6	Friday	5/6/2022	7:57 AM	11:24 AM	11:55 AM	4:30 PM	8.000						8.000
7	Saturday	5/7/2022					0.000						0.000
8	Sunday	5/8/2022					0.000						0.000
9							40.00						40.00
Sheet12

Cell Formulas
Range	Formula
G2:G8	G2	=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,M9	G9	=SUM(G2:G8)
M2:M8	M2	=LET(tol,4,act,(F2-C2-(E2-D2))*24,IF(AND(act>=(8-tol/24),act<=(8+tol/24)),8,act))

 

[KRice]

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
	A	B	C	D	E	F	G						M	N	O	P
1	Day	Date	In	out	in	out	regular						regular	regular	actual for comparison	daily variance
2	Monday	5/2/2022	7:58 AM	12:16 PM	12:47 PM	4:32 PM	8.000						8.000	8.000	8.050	0.050
3	Tuesday	5/3/2022	7:59 AM	12:28 PM	1:00 PM	4:31 PM	8.000						8.000	8.000	8.000	0.000
4	Wednesday	5/4/2022	7:58 AM	11:36 AM	12:06 PM	4:30 PM	8.000						8.000	8.000	8.033	0.033
5	Thursday	5/5/2022	7:58 AM	12:22 PM	12:53 PM	4:31 PM	8.000						8.000	8.000	8.033	0.033
6	Friday	5/6/2022	7:57 AM	11:24 AM	11:55 AM	4:30 PM	8.000						8.000	8.000	8.033	0.033
7	Saturday	5/7/2022					0.000						0.000	0.000	0.000
8	Sunday	5/8/2022					0.000						0.000	0.000	0.000
9							40.00						40.00	40.00		0.150
Sheet12

Cell Formulas
Range	Formula
M2:M8	M2	=LET(tol,4,act,(F2-C2-(E2-D2))*24,IF(AND(act>=(8-tol/24),act<=(8+tol/24)),8,act))
N2:N8	N2	=LET(tol,4,act,($F2+$D2-$C2-$E2)*24,IF(ABS(act-8)<=(tol/24),8,act))
O2:O8	O2	=($F2+$D2-$C2-$E2)*24
G2:G8	G2	=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:N9	G9	=SUM(G2:G8)
P2:P8	P2	=IF(O2<>0,O2-8,"")

 

[CathieGG]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
5
6
7
8
9	Weekly Time Sheet
10
11											5/7/22
12	Employee Name:					Dept:				Pay Period:
13
14		Date(s)	Time In	Lunch Start	Lunch End	Time Out	Regular	Vacation	Sick	Personal	Other	Total
15	Monday	5/2/22	7:24 AM	12:30 PM	1:00 PM	3:56 AM	#NAME?					0.00
16	Tuesday	5/3/22					#NAME?					0.00
17	Wednesday	5/4/22					#NAME?					0.00
18	Thursday	5/5/22					#NAME?					0.00
19	Friday	5/6/22					#NAME?					0.00
20	Saturday	5/7/22					#NAME?					0.00
21	Sunday	5/8/22					#NAME?					0.00
22	TOTALS						#NAME?	0	0	0	0	0.00
23
24									OTHER HOURS DETAIL
25								FL	Flex Time
26								H	Holiday
27	Employee Signature				Date			FH	Floating Holiday
28								JD	Jury Duty
29								BR	Bereavement Leave
30								LOA	Leave of Absence
31	Supervisor Signature				Date
32
33
34
Sheet1

Cell Formulas
Range	Formula
K11	K11	=B20
B16:B21	B16	=B15+1
G15:G21	G15	=LET(tol,7,act,(F15-C15-(E15-D15))*24,IF(AND(act>=(8-tol/24),act<=(8+tol/24)),8,act))
L15:L21	L15	=SUM(H15:K15)
G22:L22	G22	=SUM(G15:G21)

Cells with Data Validation
Cell	Allow	Criteria
C14	Any value
D14	Any value
E14	Any value
F14	Any value
B14	Any value
B15:B21	Any value
C20:F21	Any value
G14	Any value
G15:G21	Any value

 

[KRice]

The NAME error gives a clue. Are you using Excel 365?

 

[CathieGG]

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?

 

[KRice]

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:

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