Weird Time Format

[Sufiyan97]

Below is a Time format I copied from our company's system in cell first cell B2 Time is 102 hours and 30 minutes which is correct but when I see in formula bar it is showing 1/4/1900 6:30:00 AM which is not correct

I want to extract hours and minutes like 102:30 (102 hours and 30 minutes) and then I want to convert into decimals like 102.5 for 102:30

Hours will remain same but

For example
15 Minutes = 0.25
30 Minutes = 0.5
45 Minutes = 0.75

Productive hours.xlsx
	B	C
1	Amount	Expected Result
2	102:30:00	102.5
3	75:00:00	75
4	63:00:00	63
5	112:00:00
6	132:00:00
7	46:00:00
8	54:05:00
9	176:00:00
10	66:20:00
11	92:30:00
12	120:56:00
13	181:20:00
14	49:00:00
15	133:45:00
16	171:00:00
17	203:45:00
18	125:15:00	125.25
19	88:50:00
20	103:30:00
21	41:30:00
22	165:00:00
23	174:55:00
24	47:10:00
25	204:00:00
26	76:15:00
27	95:05:00
28	84:00:00
29	17:45:00
30	61:45:00
31	86:55:00
32	43:50:00
33	36:00:00
34	82:00:00
35	57:00:00
36	76:00:00
37	119:00:00
38	184:00:00
39	206:00:00
40	176:00:00
41	168:00:00
42	44:00:00
43	184:00:00
44	289:30:00
45	142:10:00
46	94:45:00
47	138:25:00
48	86:00:00
49	47:30:00
50	74:00:00
51	23:00:00
52	46:00:00
53	154:00:00
54	106:49:00
55	200:00:00
56	200:00:00
57	192:00:00
58	176:00:00
59	72:30:00
60	121:30:00
61	124:00:00
62	109:50:00
63	191:15:00
64	168:30:00
65	184:05:00
66	173:45:00
67	120:00:00
68	136:00:00
69	168:30:00
70	198:45:00
71	26:30:00
72	157:00:00
73	143:00:00
74	178:20:00
75	148:45:00
76	149:15:00
77	89:00:00
78	176:00:00
79	168:00:00
80	172:30:00
81	120:00:00
82	33:30:00
83	38:40:00
84	225:00:00
85	217:00:00
86	199:30:00
87	27:00:00
88	44:30:00
89	92:30:00
90	88:00:00
91	115:30:00
92	176:00:00
93	152:00:00
94	137:00:00
95	96:58:00
96	84:00:00
97	82:00:00
98	80:00:00
99	54:00:00
100	92:00:00
101	110:30:00
102	167:00:00
103	120:00:00
104	104:30:00
105	32:25:00
106	129:18:00
107	104:00:00
108	89:35:00
109	44:40:00
110	69:42:00
111	77:04:00
112	165:00:00
113	192:00:00
114	164:45:00
115	73:00:00
116	71:20:00
117	36:00:00
118	161:45:00
119	70:00:00
120	155:00:00
121	89:00:00
122	15:00:00
123	170:00:00
124	72:45:00
125	64:00:00
126	26:15:00
127	179:30:00
128	94:00:00
129	67:45:00
130	172:00:00
131	97:09:00
132	184:00:00
133	4:46:00
134	46:00:00
135	107:12:00
136	12:10:00
137	78:00:00
138	115:00:00
139	105:00:00
140	165:00:00
141	159:50:00
142	179:30:00
143	74:45:00
144	137:00:00
145	204:00:00
146	179:05:00
147	156:25:00
148	92:15:00
149	11:30:00
150	83:00:00
151	46:00:00
152	166:50:00
153	162:15:00
154	84:00:00
155	176:00:00
156	176:00:00
157	44:00:00
158	168:00:00
159	84:00:00
160	46:00:00
161	164:15:00
162	200:35:00
163	96:00:00
164	66:00:00
165	46:00:00
166	184:00:00
167	184:30:00
168	125:00:00
169	97:00:00
170	133:00:00
171	63:00:00
172	205:00:00
173	176:00:00
174	32:45:00
175	124:00:00
176	192:00:00
177	200:00:00
178	184:00:00
179	39:30:00
180	197:15:00
181	121:45:00
182	187:05:00
183	91:45:00
184	145:15:00
185	147:00:00
186	184:00:00
187	162:15:00
188	184:00:00
189	30:30:00
190	80:00:00
191	160:00:00
192	184:00:00
193	15:05:00
194	175:45:00
195	171:50:00
196	163:30:00
197	176:45:00
198	144:45:00
199	177:30:00
200	24:00:00
201	166:50:00
202	169:30:00
203	89:45:00
204	99:15:00
205	182:05:00
206	95:39:00
207	184:00:00
208	48:45:00
209	78:30:00
210	62:20:00
211	175:45:00
212	16:30:00
213	132:00:00
214	20:30:00
215	236:00:00
216	162:50:00
217	119:15:00
218	103:00:00
219	45:40:00
220	196:00:00
221	136:00:00
222	183:30:00
223	32:00:00
224	45:46:00
225	158:30:00
226	65:30:00
227	175:00:00
228	143:35:00
229	196:00:00
230	240:20:00
231	42:00:00
232	125:00:00
233	172:00:00
234	46:00:00
235	46:00:00
236	206:10:00
237	113:00:00
238	35:30:00
239	8:45:00
240	56:00:00
241	178:45:00
242	175:05:00
243	171:35:00
244	182:30:00
245	236:00:00
246	126:45:00
247	174:40:00
248	20:30:00
249	81:00:00
250	48:00:00
251	87:45:00
252	57:45:00
253	150:00:00
254	8:00:00
255	216:30:00
256	90:00:00
257	46:00:00
258	72:00:00
259	144:45:00
260	30:00:00
261	202:00:00
262	138:55:00
263	38:30:00
264	2:30:00
265	92:00:00
266	20:30:00
267	6:00:00
268	76:00:00
269	129:00:00
270	183:00:00
271	201:30:00
272	60:00:00
273	124:00:00
274	82:00:00
275	139:00:00
276	135:30:00
277	151:30:00
278	48:00:00
279	156:00:00
280	180:35:00
281	187:00:00
282	185:15:00
283	92:00:00
284	156:03:00
285	196:05:00
286	173:45:00
287	19:30:00
288	75:45:00
289	84:00:00
290	43:10:00
291	57:00:00
292	94:25:00
293	171:00:00
294	157:30:00
295	162:00:00
296	10:00:00
297	55:32:00
298	85:30:00
299	184:00:00
300	115:45:00
301	58:40:00
302	136:30:00
303	81:45:00
304	184:00:00
305	180:00:00
306	176:00:00
307	240:00:00
308	184:00:00
309	118:30:00
310	115:45:00
311	16:00:00
312	92:00:00
313	76:30:00
314	20:33:00
315	175:00:00
316	180:00:00
317	192:30:00
318	43:15:00
319	138:00:00
320	76:00:00
321	200:00:00
322	92:00:00
323	184:00:00
324	183:30:00
325	125:00:00
326	80:20:00
327	92:30:00
328	124:45:00
329	194:15:00
330	168:00:00
331	112:30:00
332	19:00:00
333	20:00:00
334	10:00:00
335	16:30:00
336	24:00:00
337	29:33:00
338	2:05:00
339	60:30:00
340	160:10:00
341	177:00:00
342	66:30:00
343	72:00:00
344	136:00:00
345	88:00:00
346	46:00:00
347	169:00:00
348	175:45:00
349	152:05:00
350	144:00:00
351	109:50:00
352	93:40:00
353	184:00:00
354	184:00:00
355	111:45:00
356	88:00:00
357	194:00:00
358	88:45:00
359	84:00:00
360	67:30:00
361	52:00:00
362	89:00:00
363	174:00:00
364	59:15:00
365	137:50:00
366	85:30:00
367	227:35:00
368	176:00:00
369	22:15:00
370	154:10:00
371	56:30:00
372	198:00:00
373	199:00:00
374	11:00:00
375	92:00:00
376	118:30:00
377	109:00:00
378	115:15:00
379	80:00:00
380	176:00:00
381	77:20:00
382	87:20:00
383	65:30:00
384	88:30:00
385	142:15:00
386	123:15:00
387	198:00:00
388	80:45:00
389	88:00:00
390	184:00:00
391	25:00:00
392	144:00:00
393	36:00:00
394	175:30:00
395	115:00:00
396	53:45:00
397	176:00:00
398	196:00:00
399	195:15:00
400	62:50:00
401	86:10:00
402	26:50:00
403	125:00:00
404	109:30:00
405	56:25:00
406	101:30:00
407	184:00:00
408	33:00:00
409	75:00:00
410	47:00:00
411	44:00:00
412	176:00:00
413	184:00:00
414	176:00:00
415	141:35:00
416	167:40:00
417	112:00:00
418	151:00:00
419	83:00:00
420	139:30:00
421	124:40:00
422	52:50:00
423	63:30:00
424	44:08:00
425	56:00:00
426	138:00:00
427	66:10:00
428	42:10:00
429	141:05:00
430	133:30:00
431	1:49:00
432	3:00:00
433	45:20:00
434	89:30:00
435	75:10:00
436	82:44:00
437	8:30:00
438	11:30:00
439	110:00:00
440	39:00:00
441	52:00:00
442	62:55:00
443	62:10:00
444	1:00:00
445	184:00:00
446	184:00:00
447	72:00:00
448	92:00:00
449	9:45:00
450	18:15:00
451	144:40:00
452	8:30:00
453	178:30:00
454	176:30:00
455	160:40:00
456	184:00:00
457	184:00:00
458	142:00:00
459	154:00:00
460	168:00:00
461	39:00:00
462	112:00:00
463	174:00:00
464	147:00:00
465	59:30:00
466	136:20:00
467	84:30:00
468	134:00:00
469	75:30:00
470	138:00:00
471	170:30:00
472	174:00:00
473	78:45:00
474	106:00:00
475	32:30:00
476	18:59:00
477	57:30:00
478	176:30:00
479	15:55:00
480	25:40:00
481	125:05:00
482	111:46:00
483	77:00:00
484	110:30:00
485	25:30:00
486	79:15:00
487	46:15:00
488	184:00:00
489	173:30:00
490	151:00:00
491	181:30:00
492	181:00:00
493	127:00:00
494	130:30:00
495	176:00:00
496	182:15:00
497	184:00:00
498	179:15:00
499	91:00:00
500	83:50:00
501	140:30:00
502	194:00:00
503	176:00:00
504	92:00:00
505	103:15:00
506	142:30:00
507	18:20:00
508	49:30:00
509	71:00:00
510	29:00:00
511	103:55:00
512	71:59:00
513	174:00:00
514	184:00:00
515	138:00:00
516	281:30:00
517	156:55:00
518	184:00:00
519	154:30:00
520	136:38:00
521	12:45:00
522	45:00:00
523	192:00:00
524	176:00:00
525	176:00:00
Sheet1

 

[petertenthije]

I do not want to round the time now I wanted to keep as it is in your post #2

In that case you only need to multiply the time by 24.

 

[Sufiyan97]

but can you please explain how to round the time? up and down?

What did you try?

Check Help ceiling, floor, and/or mround

I did not tried anything because I don't know about these formulas.

 

[Sufiyan97]

In that case you only need to multiply the time by 24.

Yes I got that point

 

[Dave Patton]

Date and Time 2021.xlsm
	B	C	D	E	F	G
2	102:30:00	102.5	102.50	102.50	102.50	102.50
3	54:05:00		54.08	54.25	54.00	54.00
7d

Cell Formulas
Range	Formula
D2:D3	D2	=B2*24
E2:E3	E2	=CEILING(B2*24,0.25)
F2:F3	F2	=MROUND(B2*24,0.25)
G2:G3	G2	=FLOOR(B2*24,0.25)

 

[Sufiyan97]

Date and Time 2021.xlsm
	B	C	D	E	F	G
2	102:30:00	102.5	102.50	102.50	102.50	102.50
3	54:05:00		54.08	54.25	54.00	54.00
7d

Cell Formulas
Range	Formula
D2:D3	D2	=B2*24
E2:E3	E2	=CEILING(B2*24,0.25)
F2:F3	F2	=MROUND(B2*24,0.25)
G2:G3	G2	=FLOOR(B2*24,0.25)

Thank you Dave Patton

now I got it