How to convert time (AM and PM) to number of hours

[rebel123]

Please see attached image. In the highlighted green area, I need
a calculation that would calculate time rounded up to the nearest
0.25. I am having a contractor work for me, so all time is only
like this:

12 PM
12:15 PM
12:30 PM
12:45 PM
1:00 PM


So I am not doing hours like 2:34 PM or 5:49 AM.

Please send me a formula that I can copy and drag down.
Thanks!

 

[lrobbo314]

A couple different ways.

Book1
	G	H	I
14	Time	mRound	Formula
15	5:00	5:00	5:00
16	5:01	5:00	5:00
17	5:02	5:00	5:00
18	5:03	5:00	5:00
19	5:04	5:00	5:00
20	5:05	5:00	5:00
21	5:06	5:00	5:00
22	5:07	5:00	5:00
23	5:08	5:00	5:00
24	5:09	5:00	5:00
25	5:10	5:00	5:00
26	5:11	5:00	5:00
27	5:12	5:00	5:00
28	5:13	5:25	5:25
29	5:14	5:25	5:25
30	5:15	5:25	5:25
31	5:16	5:25	5:25
32	5:17	5:25	5:25
33	5:18	5:25	5:25
34	5:19	5:25	5:25
35	5:20	5:25	5:25
36	5:21	5:25	5:25
37	5:22	5:25	5:25
38	5:23	5:25	5:25
39	5:24	5:25	5:25
40	5:25	5:25	5:25
41	5:26	5:25	5:25
42	5:27	5:25	5:25
43	5:28	5:25	5:25
44	5:29	5:25	5:25
45	5:30	5:25	5:25
46	5:31	5:25	5:25
47	5:32	5:25	5:25
48	5:33	5:25	5:25
49	5:34	5:25	5:25
50	5:35	5:25	5:25
51	5:36	5:25	5:25
52	5:37	5:25	5:25
53	5:38	5:50	5:50
54	5:39	5:50	5:50
55	5:40	5:50	5:50
56	5:41	5:50	5:50
57	5:42	5:50	5:50
58	5:43	5:50	5:50
59	5:44	5:50	5:50
60	5:45	5:50	5:50
61	5:46	5:50	5:50
62	5:47	5:50	5:50
Sheet1

Cell Formulas
Range	Formula
H15:H62	H15	=MROUND(G15,25/24/60)
I15:I62	I15	=ROUND((G15*1440)/25,0)*25/1440
G16:G62	G16	=G15+(1/24/60)

 

[rebel123]

can u attach an Excel Copy? I tried copy and pasting and it wouldnt work

 

[rebel123]

also, i really dont understand how I would input all of this in... can you just upload the entire excel formula?

To save you time, I uploaded my excel sheet here https://file.io/EfWqSgTLlDxN

 

[Tetra201]

... I am not doing hours like 2:34 PM or 5:49 AM...

So maybe just

=24*MOD(C2-B2,1)

 

[rebel123]

Thanks Tetra. That worked and I liked it and followed you.

Thanks to lrobbo314 as well as that may have worked but I was not technical enough to figure that out!
So, the error was on my behalf.

 

[Tetra201]

Glad to have helped.

 

[Dave Patton]

Consider the following.

Date and Time 2021.xlsm
	B	C	D	E
2				$ 88.00
3	10:00 AM	11:30 AM	1.50	132.00
4	10:00 AM	2:51 AM	17.00	1,496.00
5	1:00 PM	1:18 PM	0.50	44.00
6	1:00 PM	1:32 PM	0.75	66.00
7	10:00 PM	2:55 AM	5.00	440.00
8
2d

Cell Formulas
Range	Formula
D3:D7	D3	=CEILING((C3-B3+(B3>C3))*24,0.25)
E3:E7	E3	=$E$2*D3