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

 

[Dave Patton]

You did not indicate how you want to round the time.
Time is custom formatted [h]:mm:ss

Date and Time 2021.xlsm
	A	B	C	D
1		Amount	Expected Result
2		102:30:00	102.5	102.50
3		75:00:00	75	75.00
4		63:00:00	63	63.00
5		112:00:00		112.00
6		132:00:00		132.00
7		46:00:00		46.00
8		54:05:00		54.08
9		176:00:00		176.00
10		2.76388889		66.33
11		3.85416667		92.50
12		5.03888889		120.93
13		7.55555556		181.33
14		2.04166667		49.00
15		5.57291667		133.75
16		7.125		171.00
17		8.48958333		203.75
18		5.21875	125.25	125.25
7d

Cell Formulas
Range	Formula
D2:D18	D2	=B2*24

 

[Fluff]

in formula bar it is showing 1/4/1900 6:30:00 AM which is not correct

Actually it is correct it's 4 days (4*24hrs=96) + 6 hrs 30 or 102hrs 30

 

[Sufiyan97]

You did not indicate how you want to round the time.
Time is custom formatted [h]:mm:ss

Date and Time 2021.xlsm
	A	B	C	D
1		Amount	Expected Result
2		102:30:00	102.5	102.50
3		75:00:00	75	75.00
4		63:00:00	63	63.00
5		112:00:00		112.00
6		132:00:00		132.00
7		46:00:00		46.00
8		54:05:00		54.08
9		176:00:00		176.00
10		2.76388889		66.33
11		3.85416667		92.50
12		5.03888889		120.93
13		7.55555556		181.33
14		2.04166667		49.00
15		5.57291667		133.75
16		7.125		171.00
17		8.48958333		203.75
18		5.21875	125.25	125.25
7d

Cell Formulas
Range	Formula
D2:D18	D2	=B2*24

Is column D is formatted is General?

 

[Dave Patton]

You did not advise how you want to round the time!!!

Time (Amount) is custom formatted [h]:mm:ss
Is column D is formatted is General? It is formatted Number with 2 decimal points

 

[Sufiyan97]

You did not advise how you want to round the time!!!

Time (Amount) is custom formatted [h]:mm:ss
Is column D is formatted is General? It is formatted Number with 2 decimal points

Thank you very much I wanted this same as you provided

I did not understand this "You did not advise how you want to round the time!!!"
what is meaning of rounding time?

 

[Sufiyan97]

Actually it is correct it's 4 days (4*24hrs=96) + 6 hrs 30 or 102hrs 30

Got it Thank you Fluff for explaining

Is there any way to extract 102 hours and 30 minutes from it?

 

[Dave Patton]

"I did not understand this "You did not advise how you want to round the time!!!"
what is meaning of rounding time?"

Your question included For example 15 Minutes = 0.25 30 Minutes = 0.5 45 Minutes = 0.75

Do you want the time rounded up or down or to nearest 0.25 hour?

 

[Sufiyan97]

"I did not understand this "You did not advise how you want to round the time!!!"
what is meaning of rounding time?"

Your question included For example 15 Minutes = 0.25 30 Minutes = 0.5 45 Minutes = 0.75

Do you want the time rounded up or down or to nearest 0.25 hour?

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

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

 

[Dave Patton]

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

What did you try?

Check Help ceiling, floor, and/or mround