How to Roundup clock in and clock out times

[Robert Wyatt]

I would like to have a excel formula that would roundup to nearest whole number. Say I have two times and I clock in at 8:17 and clock out at 11:56 that would be 3.65 hours. and I clock in 14:56 and clock out for luch at 17:05 then clock back in from luch at 17:34 then clock out for the day at 22:18. that would be 7.85. But after subtracting the breark from the total time it show to be 10.54. How would I round it to be 10.50?

​

 

[HighAndWilder]

I would like to have a excel formula that would roundup to nearest whole number. Say I have two times and I clock in at 8:17 and clock out at 11:56 that would be 3.65 hours. and I clock in 14:56 and clock out for luch at 17:05 then clock back in from luch at 17:34 then clock out for the day at 22:18. that would be 7.85. But after subtracting the breark from the total time it show to be 10.54. How would I round it to be 10.50?

​

=TRUNC(10.54,1)

 

[Robert Wyatt]

Here is the formula I use how would I where =TRUNC(10.54,1) into it?

=ROUND(IF((OR(C14="",D14="")),0,IF((D14<C14),((D14-C14)*24)+24,(D14-C14)*24))+IF((OR(E14="",F14="")),0,IF((F14<E14),((F14-E14)*24)+24,(F14-E14)*24)),2)

 

[HighAndWilder]

Here is the formula I use how would I where =TRUNC(10.54,1) into it?

=ROUND(IF((OR(C14="",D14="")),0,IF((D14<C14),((D14-C14)*24)+24,(D14-C14)*24))+IF((OR(E14="",F14="")),0,IF((F14<E14),((F14-E14)*24)+24,(F14-E14)*24)),2)

If your formula returns the 10.54 then this :

=TRUNC(ROUND(IF((OR(C14="",D14="")),0,IF((D14<C14),((D14-C14)*24)+24,(D14-C14)*24))+IF((OR(E14="",F14="")),0,IF((F14<E14),((F14-E14)*24)+24,(F14-E14)*24)),2),1)

 

[Robert Wyatt]

That works but it show it to be round it up to much my final total hours should show to be 10.5 but it shows to be 9.0 with the =Trunc in it. it's take an 1.5 hrs away.

 

[HighAndWilder]

That works but it show it to be round it up to much my final total hours should show to be 10.5 but it shows to be 9.0 with the =Trunc in it. it's take an 1.5 hrs away.

Can you send us your data and formulas.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: https://www.mrexcel.com/board/help/xl2bb/

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.

 

[Scott Huish]

10.54 to 10.5 isn't rounding up, it's rounding down. Are you wanting to just round the total? Did you want it to round to the nearest quarter hour or what?

 

[Robert Wyatt]

Hopefully this is what you was asking for.

Book1
	A	B	C	D	E	F	G
13	Schedule	Day/Date	Shift Begin	Break Start	Break End	Shift End	Total Hours
14		Sat 10/26					0.00
15		Sun 10/27					0.00
16		Mon 10/28	8:17 AM	11:56 AM			3.65
17		Tue 10/29					0.00
18		Wed 10/30	2:56 PM	5:05 PM	5:34 PM	10:18 PM	6.88
19		Thu 10/31					0.00
20		Fri 11/01					0.00
21						Total Weekly Hours:	10.53
22						Regular Hours	10.53
23						Overtime Hours:	0.00
Sheet1

Cell Formulas
Range	Formula
B15:B20	B15	=B14+1
G14:G20	G14	=ROUND(IF((OR(C14="",D14="")),0,IF((D14<C14),((D14-C14)*24)+24,(D14-C14)*24))+IF((OR(E14="",F14="")),0,IF((F14<E14),((F14-E14)*24)+24,(F14-E14)*24)),2)
G21	G21	=SUM(G14:G20)
G22	G22	=IF(G21="","",G21-G23)
G23	G23	=IF(G21<40,0,G21-40)

 

[HighAndWilder]

In post 1 you state that there are 6 times that are to be recorded, 3 clock in and 3 clock out although the worksheet in post 8 only uses 4 times.

You also state that you want to round up to the nearest whole number but your also wanted the 10.54 to be rounded to 10.50. 11.00 would be the nearest whole number.

What do you want to do?

How to Roundup clock in and clock out times.xlsm
	B	C	D	E	F	G	H	I	J	K	L
3
4		Clock In	08:17:00			I would like to have a excel formula that would roundup to nearest whole number. Say I have two times and I clock in at 8:17 and clock out at 11:56 that would be 3.65 hours. and I clock in 14:56 and clock out for luch at 17:05 then clock back in from luch at 17:34 then clock out for the day at 22:18. that would be 7.85. But after subtracting the breark from the total time it show to be 10.54. How would I round it to be 10.50?
5		Clock Out	11:56:00	3.65
6		Break		3.00
7		Clock In	14:56:00
8		Clock Out	17:05:00	2.15
9		Break		0.48
10		Clock In	17:34:00
11		Clock Out	22:18:00	4.73
12
13			Elapsed	14.02
14			Breaks	3.48
15			Work	10.53
16
Sheet1

Cell Formulas
Range	Formula
E5,E11,E8	E5	=(D5-D4)*24
E6,E9	E6	=(D7-D5)*24
E13	E13	=($D$11-$D$4)*24
E14	E14	=((D7-D5)*24)+((D10-D8)*24)
E15	E15	=E13-E14

 

[Robert Wyatt]

I work for Dollar General and they round our time i'm not sure how or what program there using, but when I checked my pay stub today they should me to have 10.50 hrs. I on my computer where I keep my time registered it showed me to have 10.53 hrs. So to answer question what do I want to do. I have no idea. So thanks for the help I guess I'll just not worry about it. I know talking to them want help they do give out what programs they use.