Rounding Daily Totals on Time Sheets Without Changing Employee Entries

C

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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Column L shows how to round to the 5 minutes. But maybe this isn't what you really want.

MrExcelPlayground8-1.xlsx
ABCDEFGHIJKL
1DayDateInoutinoutregularvacasickpersonalothertotal
2Monday5/2/20227:5812:1612:4716:328.058.00
3Tuesday5/3/20227:5912:2813:0016:318.008.00
4Wednesday5/4/20227:5811:3612:0616:308.038.00
5Thursday5/5/20227:5812:2212:5316:318.037.92
6Friday5/6/20227:5711:2411:5516:308.038.08
7Saturday5/7/20220.000.00
8Sunday5/8/20220.000.00
940.1540.00
Sheet24
Cell Formulas
RangeFormula
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
Welcome to the MrExcel board, Cathie. As James points out, conventional rounding may not be what you want. For example, the Friday Time In would round down to 7:55 AM (if to the nearest 5 minutes), but you mentioned that it should be rounded to 8:00 AM. Do you want the "in" times (time in and lunch end) to round up and the "out" times (time out and lunch start) to round down?
 
Upvote 0
Hi James - I think this is exactly what I want, but my M column is still not totaling:

=(MROUND(F15,5/60/24)-MROUND(C15,5/60/24)-(MROUND(E15,5/60/24)-MROUND(D15,5/60/24)))*24

1652703737378.png
 
Upvote 0
I cannot reproduce your "FALSE" in M15. Any chance you can download xl2bb? It's very helpful. Yours aught to work...

But any chance that in C15-F15 you have some kind of non-time data that just looks like time or some other weirdness?
 
Upvote 0
Time Sheet Testing.xlsx
ABCDEFGHIJKLM
1
2
3
4
5
6
7
8
9Weekly Time Sheet
10
11
12Employee Name: Dept: Pay Period:
13
14Date(s)Time InLunch StartLunch EndTime OutRegularColumn1VacationSickPersonalOtherTotal
15Monday5/2/227:2712:0012:3016:058.13FALSE
16Tuesday5/3/220.000.00
17Wednesday5/4/220.000.00
18Thursday5/5/220.000.00
19Friday5/6/220.000.00
20Saturday5/7/220.000.00
21Sunday5/8/220.000.00
22TOTALS8.1300000.00
23
24OTHER HOURS DETAIL
25FLFlex Time
26HHoliday
27Employee SignatureDateFHFloating Holiday
28JDJury Duty
29BRBereavement Leave
30LOALeave of Absence
Sheet1
Cell Formulas
RangeFormula
B16:B21B16=B15+1
M15M15=(MROUND(F15,5/60/24)-MROUND(C15,5/60/24)-(MROUND(E15,5/60/24)-MROUND(D15,5/60/24)))*24 =(MROUND(F2,5/60/24)-MROUND(C15,5/60/24)-(MROUND(E15,5/60/24)-MROUND(D15,5/60/24)))*24 =(MROUND(F15,5/60/24)-MROUND(C15,5/60/24)-(MROUND(E15,5/60/24)-MROUND(D2,5/60/24)))*2
M16:M21M16=SUM(G16:L16)
G15:G21G15=(F15-C15-(E15-D15))*24
G22,I22:M22G22=SUM(G15:G21)
Cells with Data Validation
CellAllowCriteria
B14Any value
C14Any value
D14Any value
E14Any value
F14Any value
G14:H14Any value
B15:H21Any value
 
Upvote 0
I see what happened. Look at M15. There are four lines of equations in the one cell (including a blank line).
MrExcelPlayground8-1.xlsx
ABCDEFGHIJKLM
14Date(s)Time InLunch StartLunch EndTime OutRegularColumn1VacationSickPersonalOtherTotal
15Monday5/2/20227:27:00 AM12:00:00 PM12:30:00 PM4:05:00 PM8.13338.16666667
16Tuesday5/3/20227:27:00 AM12:00:00 PM12:30:00 PM4:05:00 PM8.13338.16666667
17Wednesday5/4/202200
18Thursday5/5/202200
19Friday5/6/202200
20Saturday5/7/202200
21Sunday5/8/202200
22TOTALS16.267000016.3333333
Sheet26
Cell Formulas
RangeFormula
B16:B21B16=B15+1
M15:M21M15=(MROUND(F15,5/60/24)-MROUND(C15,5/60/24)-(MROUND(E15,5/60/24)-MROUND(D15,5/60/24)))*24
G15:G21G15=(F15-C15-(E15-D15))*24
G22,I22:M22G22=SUM(G15:G21)
 
Upvote 0
Thanks James. Not sure how that happened because it was only showing me one line. I copy and pasted an empty cell, and then pasted the one line. Perfect.

Thank you for your patience.
 
Upvote 0
Sorry James - One more question. On the Thursday and Friday rows, how do I get their totals to round to an even 8 hrs?
JamesCanale said:
Column L shows how to round to the 5 minutes. But maybe this isn't what you really want.

MrExcelPlayground8-1.xlsx
ABCDEFGHIJKL
1DayDateInoutinoutregularvacasickpersonalothertotal
2Monday5/2/20227:5812:1612:4716:328.058.00
3Tuesday5/3/20227:5912:2813:0016:318.008.00
4Wednesday5/4/20227:5811:3612:0616:308.038.00
5Thursday5/5/20227:5812:2212:5316:318.037.92
6Friday5/6/20227:5711:2411:5516:308.038.08
7Saturday5/7/20220.000.00
8Sunday5/8/20220.000.00
940.1540.00
Sheet24
Cell Formulas
RangeFormula
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
Click to expand...
 
Upvote 0
That's what krice was talking about. If you want it to the nearest 5 minutes, you don't get it to round to an even 8 hours.

The question is, what do you want to do. Once you start rounding, you'll have problems. You can round down the outs and round up the ins.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Rounding Negative time to the nearest minute in Excel
Replies
4
Views
86
Scott Huish
Scott Huish
M
  • Solved
When do I need to clock out?
Replies
4
Views
1K
jtakw
jtakw
S
  • Question
Formula to Calculate Time with Multiple Round Rules
Replies
1
Views
1K
earthworm
earthworm
R
  • Question
Vlookup
Replies
4
Views
352
Robert Wyatt
R
K
  • Solved
Sum function formula or alternate
Replies
3
Views
563
Fluff
Fluff

Forum statistics

Threads
1,214,932
Messages
6,122,332
Members
449,077
Latest member
jmsotelo

Share this page

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