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?

​

 

[Robert Wyatt]

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?

I'm needing it to round to the nearest 10 Minutes I clock in at 55 minutes or after the hour. I also need it to round to the nearest 15 to 30 minutes. If I clock in at say 8:55 I need to round to 8:00. if I clock out and say 8:05 then I would need it to round to 8:00. thing more than 5 Mins to to round to the next 15 minutes.

 

[Scott Huish]

Does this work for you?

=MROUND(A1+"0:05","0:15")

 

[Robert Wyatt]

Does this work for you?

=MROUND(A1+"0:05","0:15")

I'm not sure. How would I install it into my formula (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)

 

[Scott Huish]

First off I would suggest simplifying that. Why do you have so many IFs?

 

[bebo021999]

Help request - formula simplification.xlsb
	A	B	C	D	E	F	G
13	Schedule	Day/Date	Shift Begin	Break Start	Break End	Shift End	Total Hours
14		26 Oct 2024					-
15		27 Oct 2024					-
16		28 Oct 2024	08:17:00	11:56:00			3.60
17		29 Oct 2024					-
18		30 Oct 2024	14:56:00	17:05:00	17:34:00	22:18:00	6.90
19		31 Oct 2024					-
20		1 Nov 2024					-
21						Total Weekly Hours:	10.50
22						Regular Hours	10.50
23						Overtime Hours:	-
Sheet1

Cell Formulas
Range	Formula
B15:B20	B15	=B14+1
G14:G20	G14	=MROUND(D14-C14+F14-E14+AND(F14<C14,F14>0),15/3600)*24
G21	G21	=SUM(G14:G20)
G22	G22	=IF(G21="","",G21-G23)
G23	G23	=IF(G21<40,0,G21-40)

 

[Robert Wyatt]

Help request - formula simplification.xlsb
	A	B	C	D	E	F	G
13	Schedule	Day/Date	Shift Begin	Break Start	Break End	Shift End	Total Hours
14		26 Oct 2024					-
15		27 Oct 2024					-
16		28 Oct 2024	08:17:00	11:56:00			3.60
17		29 Oct 2024					-
18		30 Oct 2024	14:56:00	17:05:00	17:34:00	22:18:00	6.90
19		31 Oct 2024					-
20		1 Nov 2024					-
21						Total Weekly Hours:	10.50
22						Regular Hours	10.50
23						Overtime Hours:	-
Sheet1

Cell Formulas
Range	Formula
B15:B20	B15	=B14+1
G14:G20	G14	=MROUND(D14-C14+F14-E14+AND(F14<C14,F14>0),15/3600)*24
G21	G21	=SUM(G14:G20)
G22	G22	=IF(G21="","",G21-G23)
G23	G23	=IF(G21<40,0,G21-40)

This works, but when I copied and pasted it into excel it changed some how. Where you showed 3.60 hrs. in mine it shows 3.70, but the other times shows the same as in yours.

 

[bebo021999]

Where you showed 3.60 hrs. in mine it shows 3.70, but the other times shows the same as in yours.

Pls show your result, in XL2BB or image.

 

[Robert Wyatt]

Pls show your result, in XL2BB or image.

I got it worked out everything is working good thanks.