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