Convert to minute elapsed from specific field

[darzu]

Hello,

i got a list of tickets with the dedicated time for each, i want to convert it in excel so i can sum the time consumed per week, but the time comsumption needs to be converted i wanted to make with if functions but maybe there is a more easier way, i identified 3 variants:

-1h
-1h 30m
-30m

Thanks

report-listare_etichete_2020_10_20.xls
	A	B	C
5	Ticket #	Total Time Spent by User	Create Date
6	5532	4h	03-08-2020 @ 14:10:48
7	5538	30m	03-08-2020 @ 22:24:58
8	5557	15m	04-08-2020 @ 20:32:45
9	5568	45m	05-08-2020 @ 09:10:46
10	5568	45m	05-08-2020 @ 09:10:46
11	5569	30m	05-08-2020 @ 11:16:45
12	5585	1h	06-08-2020 @ 12:00:45
13	5591	30m	06-08-2020 @ 16:08:46
14	5592	1h	06-08-2020 @ 16:42:52
15	5593	30m	06-08-2020 @ 17:14:45
16	5595	15m	06-08-2020 @ 21:22:50
17	5596	30m	07-08-2020 @ 00:35:51
18	5603	15m	07-08-2020 @ 20:55:22
19	5605	1h	07-08-2020 @ 21:45:03
20	5607	30m	07-08-2020 @ 23:51:13
21	5615	45m	10-08-2020 @ 12:40:27
22	5639	15m	12-08-2020 @ 05:25:53
23	5667	30m	13-08-2020 @ 09:08:30
24	5670	15m	13-08-2020 @ 11:24:28
25	5676	1h 30m	13-08-2020 @ 15:40:28
26	5677	30m	13-08-2020 @ 15:58:31
27	5708	15m	17-08-2020 @ 12:01:29
28	5708	15m	17-08-2020 @ 12:01:29
29	5722	30m	18-08-2020 @ 14:03:29
30	5722	1h	18-08-2020 @ 14:03:29
31	5738	30m	19-08-2020 @ 09:13:28
32	5765	30m	20-08-2020 @ 11:05:31
33	5766	0m	20-08-2020 @ 11:11:31
34	5767	20m	20-08-2020 @ 12:11:29
35	5771	30m	20-08-2020 @ 14:45:28
36	5776	1h 30m	20-08-2020 @ 17:21:28
37	5777	30m	20-08-2020 @ 17:29:28
38	5781	30m	20-08-2020 @ 21:21:28
39	5786	0m	21-08-2020 @ 11:19:29
40	5787	15m	21-08-2020 @ 11:19:33
41	5788	0m	21-08-2020 @ 11:19:36
42	5789	20m	21-08-2020 @ 12:11:28
43	5808	15m	24-08-2020 @ 12:06:18
44	5829	1h	25-08-2020 @ 06:26:40
45	5830	30m	25-08-2020 @ 06:28:39
46	5831	1h	25-08-2020 @ 06:29:42
47	5849	30m	25-08-2020 @ 22:11:07
48	5867	30m	27-08-2020 @ 02:29:33
49	5868	30m	27-08-2020 @ 02:30:28
50	5879	45m	27-08-2020 @ 12:46:17
51	5880	15m	27-08-2020 @ 13:12:16
52	5881	30m	27-08-2020 @ 13:14:17
53	5882	20m	27-08-2020 @ 13:28:18
54	5885	45m	27-08-2020 @ 15:56:19
55	5886	1h 30m	27-08-2020 @ 16:02:17
56	5887	45m	27-08-2020 @ 16:10:17
57	5900	15m	28-08-2020 @ 13:42:21
58	5903	30m	28-08-2020 @ 20:50:18
59	5910	15m	29-08-2020 @ 10:22:17
60	5959	15m	02-09-2020 @ 13:01:04
61	5978	1h	03-09-2020 @ 18:41:02
62	5979	45m	03-09-2020 @ 18:43:02
63	5986	30m	04-09-2020 @ 09:23:03
64	6008	30m	07-09-2020 @ 11:18:59
65	6014	30m	07-09-2020 @ 16:16:55
66	6066	45m	10-09-2020 @ 13:18:55
67	6072	15m	10-09-2020 @ 15:19:19
68	6074	1h 15m	10-09-2020 @ 16:18:56
69	6081	1h 15m	11-09-2020 @ 10:06:55
70	6083	1h	11-09-2020 @ 11:21:01
71	6087	30m	11-09-2020 @ 19:40:54
72	6091	0m	12-09-2020 @ 11:56:55
73	6096	15m	14-09-2020 @ 08:01:37
74	6104	30m	14-09-2020 @ 19:27:35
75	6107	15m	14-09-2020 @ 23:59:35
76	6110	30m	15-09-2020 @ 09:05:37
77	6113	2h	15-09-2020 @ 13:05:36
78	6114	15m	15-09-2020 @ 13:43:36
79	6138	1h 15m	16-09-2020 @ 11:45:40
80	6139	1h 15m	16-09-2020 @ 11:47:35
81	6140	30m	16-09-2020 @ 12:16:06
82	6141	1h 30m	16-09-2020 @ 12:20:29
83	6142	15m	16-09-2020 @ 13:55:36
84	6145	1h	16-09-2020 @ 16:13:35
85	6146	45m	16-09-2020 @ 16:25:35
86	6159	30m	17-09-2020 @ 12:00:12
87	6163	1h	17-09-2020 @ 18:27:35
88	6164	15m	17-09-2020 @ 18:29:35
89	6165	1h 15m	17-09-2020 @ 18:33:35
90	6179	30m	18-09-2020 @ 11:21:36
91	6181	30m	18-09-2020 @ 14:39:35
92	6196	30m	21-09-2020 @ 14:43:25
93	6206	1h	22-09-2020 @ 09:33:26
94	6206	1h	22-09-2020 @ 09:33:26
95	6231	30m	23-09-2020 @ 14:33:54
96	6233	30m	23-09-2020 @ 15:03:27
97	6244	15m	24-09-2020 @ 15:37:25
98	6245	15m	24-09-2020 @ 17:23:24
99	6246	1h	24-09-2020 @ 17:55:25
100	6253	15m	25-09-2020 @ 08:23:25
101	6256	45m	25-09-2020 @ 13:51:24
102	6257	30m	25-09-2020 @ 14:29:25
103	6281	30m	28-09-2020 @ 17:12:09
104	6282	1h	28-09-2020 @ 17:16:07
105	6296	45m	29-09-2020 @ 09:22:07
106	6313	45m	29-09-2020 @ 14:24:07
107	6319	30m	29-09-2020 @ 18:08:07
108	6320	30m	29-09-2020 @ 18:08:09
109	6331	30m	30-09-2020 @ 14:46:17
110	6338	45m	30-09-2020 @ 17:08:07
111	6339	30m	30-09-2020 @ 17:08:09
112	6353	15m	01-10-2020 @ 13:26:10
113	6358	30m	01-10-2020 @ 16:06:07
114	6359	3h	01-10-2020 @ 16:50:07
115	6360	1h	01-10-2020 @ 16:52:07
116	6361	1h 30m	01-10-2020 @ 17:20:07
117	6382	2h 30m	02-10-2020 @ 21:32:09
118	6386	30m	03-10-2020 @ 09:38:06
119	6399	30m	05-10-2020 @ 12:25:35
120	6426	1h	07-10-2020 @ 07:35:18
121	6430	30m	07-10-2020 @ 11:23:18
122	6436	30m	07-10-2020 @ 15:55:39
123	6446	30m	08-10-2020 @ 12:37:18
124	6447	1h	08-10-2020 @ 12:41:18
125	6453	30m	08-10-2020 @ 16:36:09
126	6454	1h	08-10-2020 @ 17:35:18
127	6455	30m	08-10-2020 @ 17:37:17
128	6455	15m	08-10-2020 @ 17:37:17
129	6456	45m	08-10-2020 @ 17:47:18
130	6473	30m	09-10-2020 @ 19:33:41
131	6475	15m	12-10-2020 @ 05:16:27
132	6479	45m	12-10-2020 @ 12:26:29
133	6482	15m	12-10-2020 @ 16:46:28
134	6483	15m	12-10-2020 @ 16:58:26
135	6489	15m	13-10-2020 @ 13:29:14
136	6490	15m	13-10-2020 @ 13:46:26
137	6503	15m	14-10-2020 @ 12:16:27
138	6514	15m	14-10-2020 @ 21:48:31
139	6516	15m	15-10-2020 @ 01:50:24
140	6517	45m	15-10-2020 @ 02:44:43
141	6529	30m	15-10-2020 @ 15:40:52
142	6539	30m	16-10-2020 @ 07:12:26
143	6551	30m	16-10-2020 @ 21:43:19
144	6556	30m	19-10-2020 @ 07:05:24
145	6559	1h 30m	19-10-2020 @ 08:49:25
146	6560	1h	19-10-2020 @ 08:51:23
147	6563	30m	19-10-2020 @ 09:59:24
148	6574	15m	19-10-2020 @ 20:29:32
149	6575	15m	19-10-2020 @ 20:55:24
150	6577	45m	20-10-2020 @ 00:33:24
151	6578	15m	20-10-2020 @ 00:35:24
152	6579	15m	20-10-2020 @ 00:35:26
153	6581	30m	20-10-2020 @ 09:25:24
154	6591	15m	20-10-2020 @ 15:20:24
155	6591	15m	20-10-2020 @ 15:20:24
156	6593	45m	20-10-2020 @ 16:11:24
Spiceworks Report

 

[AlanY]

this will convert Col B to mins

Book1
	A	B	C	D	E
1	Ticket #	Total Time Spent by User	Create Date		Mins
2	5532	4h	03-08-2020 @ 14:10:48		240
3	5538	30m	03-08-2020 @ 22:24:58		30
4	5557	15m	04-08-2020 @ 20:32:45		15
5	5568	45m	05-08-2020 @ 09:10:46		45
6	5568	45m	05-08-2020 @ 09:10:46		45
7	5569	30m	05-08-2020 @ 11:16:45		30
8	5585	1h	06-08-2020 @ 12:00:45		60
9	5591	30m	06-08-2020 @ 16:08:46		30
10	5592	1h	06-08-2020 @ 16:42:52		60
11	5593	30m	06-08-2020 @ 17:14:45		30
12	5595	15m	06-08-2020 @ 21:22:50		15
13	5596	30m	07-08-2020 @ 00:35:51		30
14	5603	15m	07-08-2020 @ 20:55:22		15
15	5605	1h	07-08-2020 @ 21:45:03		60
16	5607	30m	07-08-2020 @ 23:51:13		30
17	5615	45m	10-08-2020 @ 12:40:27		45
18	5639	15m	12-08-2020 @ 05:25:53		15
19	5667	30m	13-08-2020 @ 09:08:30		30
20	5670	15m	13-08-2020 @ 11:24:28		15
21	5676	1h 30m	13-08-2020 @ 15:40:28		90
22	5677	30m	13-08-2020 @ 15:58:31		30
Sheet3

Cell Formulas
Range	Formula
E2:E22	E2	=IFERROR(SUBSTITUTE(LEFT(B2,3),"h","")*60,0)+IFERROR(SUBSTITUTE(RIGHT(B2,3),"m","")*1,0)

 

[darzu]

Thanks AlanY this solved my issue, you are great!

 

[AlanY]

you're welcome