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

 

[KRice]

I'm sorry Cathie, but something just occurred to me when I looked back at this. The basic approach and formula is fine except for one detail. When the daily time "in" is computed based on adding and subtracting clock times, Excel treats those clock times as fractions of a 24-hour day...and that is why we multiply by 24 to convert the time "in" that is initially expressed in days into units of hours. But when the tolerance comparison is made, we compute the difference between the actual time "in" and 8 hours....and that difference is expressed in units of hours. Therefore the tolerance expression needs to also be in units of hours. So 7 minutes would be divided by 60 min/hr for the correct conversion...like this...rather than 24 in the earlier post...so just change the 7/24 to 7/60 and leave the other 24's alone.

=IF(ABS(24*($F15+$D15-$C15-$E15)-8)<=(7/60),8,24*($F15+$D15-$C15-$E15))

 

[KRice]

Cathie,

I hope you will indulge a little more discussion about your question because a few more things have occurred to me. Before putting a formula to use, I like to investigate its behavior at certain key points to see if there are any surprises. In this case there is a surprise. With the last formula, if you set up in/out hours for a day such that the total "in" time is 7:53 (h:mm), which is right at the decision point where the 7 minute tolerance rule says this is close enough to 8:00 hours...so the formula should return 8:00, you will discover that the formula returns 7:53 (h:mm format), or 7.88 hours, suggesting that the total "in" time is too low...see the yellow cell. What happened? You have encountered one of those instances where Excel's numerical precision is responsible.

Excel largely adheres to a widely used standard, IEEE 754, for storing numbers, but there are two issues that can cause some surprises: either due to Excel expressing numbers with 15 significant digits or Excel's internal conversion of numbers into binary format, where number storage capacity may also create a problem. A common example is the decimal number 0.1, where there is no issue expressing it with just one significant digit, but Excel stores this in binary format as 0.000110011001100...with the "1100" repeating. Due to the number of bits available, the value is internally rounded, potentially causing an unexpected result. These surprises are sometimes encountered during logical tests, as in your case.

The easiest protection against this is to enforce a final rounding of your tolerance, perhaps to 4 decimal places...see the orange cell. So a 7 minute tolerance is 7/60 hours, or 0.11666666...hours (the 6 is repeating), which would effectively become 0.1167 exactly; and this is the same as your tolerance value within a fraction of a second. The orange cell includes the rounding just described to address this potential numerical precision issue.

Next, if you have a "no overtime" policy and do not want to see anyone's daily time "in" excursions beyond 8:07 hours (h:mm) (which the orange formula will show as illustrated in the red cells), then you could add one more enhancement to the formula. The enhancement displays any short times less than 8-tol, but for the cases of anything beyond 8+tol, it still limits the time reported back as 8. That enhancement places the actual time "in" inside a MIN function and returns the minimum of either 8 or the actual time. The combination of the two enhancements (rounding to avoid a numerical precision issue when the tolerance is checked) and rounding any long times to 8 is shown in the green cells with some examples.

MrExcel_20220518a.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
9	Weekly Time Sheet
10
11											5/7/2022
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/2022	7:30 AM	12:30 PM	1:00 PM	3:53 PM	7.88					0
16	Tuesday	5/3/2022	7:30 AM	12:30 PM	1:00 PM	3:53 PM	8.00					0
17	Wednesday	5/4/2022	7:30 AM	12:30 PM	1:00 PM	4:07 PM	8.00					0
18	Thursday	5/5/2022	7:30 AM	12:30 PM	1:00 PM	4:08 PM	8.13					0
19	Friday	5/6/2022	7:30 AM	12:30 PM	1:00 PM	3:53 PM	8.00					0
20	Saturday	5/7/2022	7:30 AM	12:30 PM	1:00 PM	3:52 PM	7.87					0
21	Sunday	5/8/2022	7:30 AM	12:30 PM	1:00 PM	4:08 PM	8.00					0
22	TOTALS						55.88	0	0	0	0	0
Cathie

Cell Formulas
Range	Formula
K11	K11	=B20
B16:B21	B16	=B15+1
G15	G15	=IF(ABS(24*($F15+$D15-$C15-$E15)-8)<=(7/60),8,24*($F15+$D15-$C15-$E15))
G16:G18	G16	=IF(ABS(24*($F16+$D16-$C16-$E16)-8)<=ROUND(7/60,4),8,24*($F16+$D16-$C16-$E16))
G19:G21	G19	=IF(ABS(24*($F19+$D19-$C19-$E19)-8)<=ROUND(7/60,4),8,MIN(8,24*($F19+$D19-$C19-$E19)))
L15:L21	L15	=SUM(H15:K15)
G22:L22	G22	=SUM(G15:G21)