Round Up and Round Down based on certain decimal in a single formula

[slothmgr]

Hi, first of all I am sorry if my question (and its answer) has been posted here. Please kindly direct me to the post if it's already answered. And I'm sorry if my explanation is hard to understand.

So I'm working on a data of employee's working hours by hour. And I want if the working hours of them is bigger than or equal to XX:40, then it's rounded up. And if it's less than XX:40, then it's rounded down.


For example,
Working hours 7:40 ー> 8:00
Working hours 6:39 ー> 6:00

I was thinking of to convert the hours to number format, so any decimal number bigger than decimal of xx.67 (xx:40) it should be rounded up, and if it's less than xx.67 (xx:40) it should be rounded down. But then I don't how to set the limit of the xx.67. And should I combine it with IF function?


Is there anyway to solve this? Thank you in advance!

 

[JamesCanale]

MrExcelPlayground7.xlsx
	A	B
2	7:00	7:00
3	7:01	7:00
4	7:02	7:00
5	7:03	7:00
6	7:04	7:00
7	7:05	7:00
8	7:06	7:00
9	7:07	7:00
10	7:08	7:00
11	7:09	7:00
12	7:10	7:00
13	7:11	7:00
14	7:12	7:00
15	7:13	7:00
16	7:14	7:00
17	7:15	7:00
18	7:16	7:00
19	7:17	7:00
20	7:18	7:00
21	7:19	7:00
22	7:20	7:00
23	7:21	7:00
24	7:22	7:00
25	7:23	7:00
26	7:24	7:00
27	7:25	7:00
28	7:26	7:00
29	7:27	7:00
30	7:28	7:00
31	7:29	7:00
32	7:30	7:00
33	7:31	7:00
34	7:32	7:00
35	7:33	7:00
36	7:34	7:00
37	7:35	7:00
38	7:36	7:00
39	7:37	7:00
40	7:38	7:00
41	7:39	7:00
42	7:40	8:00
43	7:41	8:00
44	7:42	8:00
45	7:43	8:00
46	7:44	8:00
47	7:45	8:00
48	7:46	8:00
49	7:47	8:00
50	7:48	8:00
51	7:49	8:00
52	7:50	8:00
53	7:51	8:00
54	7:52	8:00
55	7:53	8:00
56	7:54	8:00
57	7:55	8:00
58	7:56	8:00
59	7:57	8:00
60	7:58	8:00
61	7:59	8:00
62	8:00	8:00
63	8:01	8:00
64	8:02	8:00
65	8:03	8:00
66	8:04	8:00
67	8:05	8:00
68	8:06	8:00
69	8:07	8:00
70	8:08	8:00
Sheet8

Cell Formulas
Range	Formula
B2:B70	B2	=TIME(HOUR(A2+20/60/24),0,0)

 

[Joe4]

Welcome to the Board!

If you want a numeric value (i.e. number of hours) and not a date/time returned, then for a time in cell A1, use this formula:

=HOUR(A1)+IF(MINUTE(A1)>=40,1,0)

and make sure the result is formatted as a number and not date/time.

 

[slothmgr]

MrExcelPlayground7.xlsx
	A	B
2	7:00	7:00
3	7:01	7:00
4	7:02	7:00
5	7:03	7:00
6	7:04	7:00
7	7:05	7:00
8	7:06	7:00
9	7:07	7:00
10	7:08	7:00
11	7:09	7:00
12	7:10	7:00
13	7:11	7:00
14	7:12	7:00
15	7:13	7:00
16	7:14	7:00
17	7:15	7:00
18	7:16	7:00
19	7:17	7:00
20	7:18	7:00
21	7:19	7:00
22	7:20	7:00
23	7:21	7:00
24	7:22	7:00
25	7:23	7:00
26	7:24	7:00
27	7:25	7:00
28	7:26	7:00
29	7:27	7:00
30	7:28	7:00
31	7:29	7:00
32	7:30	7:00
33	7:31	7:00
34	7:32	7:00
35	7:33	7:00
36	7:34	7:00
37	7:35	7:00
38	7:36	7:00
39	7:37	7:00
40	7:38	7:00
41	7:39	7:00
42	7:40	8:00
43	7:41	8:00
44	7:42	8:00
45	7:43	8:00
46	7:44	8:00
47	7:45	8:00
48	7:46	8:00
49	7:47	8:00
50	7:48	8:00
51	7:49	8:00
52	7:50	8:00
53	7:51	8:00
54	7:52	8:00
55	7:53	8:00
56	7:54	8:00
57	7:55	8:00
58	7:56	8:00
59	7:57	8:00
60	7:58	8:00
61	7:59	8:00
62	8:00	8:00
63	8:01	8:00
64	8:02	8:00
65	8:03	8:00
66	8:04	8:00
67	8:05	8:00
68	8:06	8:00
69	8:07	8:00
70	8:08	8:00
Sheet8

Cell Formulas
Range	Formula
B2:B70	B2	=TIME(HOUR(A2+20/60/24),0,0)

Welcome to the Board!

If you want a numeric value (i.e. number of hours) and not a date/time returned, then for a time in cell A1, use this formula:

=HOUR(A1)+IF(MINUTE(A1)>=40,1,0)

and make sure the result is formatted as a number and not date/time.

Thank you for the warm welcoming words!

Thank you so much for both of you. They worked perfectly! Now I know how to round the time (with a certain limit) both in number-formatted and time-formatted.

I wish you two have a nice day!?

 

[Joe4]

You are welcome.
Glad we were able to help!