Convert to minute elapsed from specific field

darzu

New Member
Joined
Jul 13, 2020
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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
ABC
5Ticket #Total Time Spent by UserCreate Date
655324h03-08-2020 @ 14:10:48
7553830m03-08-2020 @ 22:24:58
8555715m04-08-2020 @ 20:32:45
9556845m05-08-2020 @ 09:10:46
10556845m05-08-2020 @ 09:10:46
11556930m05-08-2020 @ 11:16:45
1255851h06-08-2020 @ 12:00:45
13559130m06-08-2020 @ 16:08:46
1455921h06-08-2020 @ 16:42:52
15559330m06-08-2020 @ 17:14:45
16559515m06-08-2020 @ 21:22:50
17559630m07-08-2020 @ 00:35:51
18560315m07-08-2020 @ 20:55:22
1956051h07-08-2020 @ 21:45:03
20560730m07-08-2020 @ 23:51:13
21561545m10-08-2020 @ 12:40:27
22563915m12-08-2020 @ 05:25:53
23566730m13-08-2020 @ 09:08:30
24567015m13-08-2020 @ 11:24:28
2556761h 30m13-08-2020 @ 15:40:28
26567730m13-08-2020 @ 15:58:31
27570815m17-08-2020 @ 12:01:29
28570815m17-08-2020 @ 12:01:29
29572230m18-08-2020 @ 14:03:29
3057221h18-08-2020 @ 14:03:29
31573830m19-08-2020 @ 09:13:28
32576530m20-08-2020 @ 11:05:31
3357660m20-08-2020 @ 11:11:31
34576720m20-08-2020 @ 12:11:29
35577130m20-08-2020 @ 14:45:28
3657761h 30m20-08-2020 @ 17:21:28
37577730m20-08-2020 @ 17:29:28
38578130m20-08-2020 @ 21:21:28
3957860m21-08-2020 @ 11:19:29
40578715m21-08-2020 @ 11:19:33
4157880m21-08-2020 @ 11:19:36
42578920m21-08-2020 @ 12:11:28
43580815m24-08-2020 @ 12:06:18
4458291h25-08-2020 @ 06:26:40
45583030m25-08-2020 @ 06:28:39
4658311h25-08-2020 @ 06:29:42
47584930m25-08-2020 @ 22:11:07
48586730m27-08-2020 @ 02:29:33
49586830m27-08-2020 @ 02:30:28
50587945m27-08-2020 @ 12:46:17
51588015m27-08-2020 @ 13:12:16
52588130m27-08-2020 @ 13:14:17
53588220m27-08-2020 @ 13:28:18
54588545m27-08-2020 @ 15:56:19
5558861h 30m27-08-2020 @ 16:02:17
56588745m27-08-2020 @ 16:10:17
57590015m28-08-2020 @ 13:42:21
58590330m28-08-2020 @ 20:50:18
59591015m29-08-2020 @ 10:22:17
60595915m02-09-2020 @ 13:01:04
6159781h03-09-2020 @ 18:41:02
62597945m03-09-2020 @ 18:43:02
63598630m04-09-2020 @ 09:23:03
64600830m07-09-2020 @ 11:18:59
65601430m07-09-2020 @ 16:16:55
66606645m10-09-2020 @ 13:18:55
67607215m10-09-2020 @ 15:19:19
6860741h 15m10-09-2020 @ 16:18:56
6960811h 15m11-09-2020 @ 10:06:55
7060831h11-09-2020 @ 11:21:01
71608730m11-09-2020 @ 19:40:54
7260910m12-09-2020 @ 11:56:55
73609615m14-09-2020 @ 08:01:37
74610430m14-09-2020 @ 19:27:35
75610715m14-09-2020 @ 23:59:35
76611030m15-09-2020 @ 09:05:37
7761132h15-09-2020 @ 13:05:36
78611415m15-09-2020 @ 13:43:36
7961381h 15m16-09-2020 @ 11:45:40
8061391h 15m16-09-2020 @ 11:47:35
81614030m16-09-2020 @ 12:16:06
8261411h 30m16-09-2020 @ 12:20:29
83614215m16-09-2020 @ 13:55:36
8461451h16-09-2020 @ 16:13:35
85614645m16-09-2020 @ 16:25:35
86615930m17-09-2020 @ 12:00:12
8761631h17-09-2020 @ 18:27:35
88616415m17-09-2020 @ 18:29:35
8961651h 15m17-09-2020 @ 18:33:35
90617930m18-09-2020 @ 11:21:36
91618130m18-09-2020 @ 14:39:35
92619630m21-09-2020 @ 14:43:25
9362061h22-09-2020 @ 09:33:26
9462061h22-09-2020 @ 09:33:26
95623130m23-09-2020 @ 14:33:54
96623330m23-09-2020 @ 15:03:27
97624415m24-09-2020 @ 15:37:25
98624515m24-09-2020 @ 17:23:24
9962461h24-09-2020 @ 17:55:25
100625315m25-09-2020 @ 08:23:25
101625645m25-09-2020 @ 13:51:24
102625730m25-09-2020 @ 14:29:25
103628130m28-09-2020 @ 17:12:09
10462821h28-09-2020 @ 17:16:07
105629645m29-09-2020 @ 09:22:07
106631345m29-09-2020 @ 14:24:07
107631930m29-09-2020 @ 18:08:07
108632030m29-09-2020 @ 18:08:09
109633130m30-09-2020 @ 14:46:17
110633845m30-09-2020 @ 17:08:07
111633930m30-09-2020 @ 17:08:09
112635315m01-10-2020 @ 13:26:10
113635830m01-10-2020 @ 16:06:07
11463593h01-10-2020 @ 16:50:07
11563601h01-10-2020 @ 16:52:07
11663611h 30m01-10-2020 @ 17:20:07
11763822h 30m02-10-2020 @ 21:32:09
118638630m03-10-2020 @ 09:38:06
119639930m05-10-2020 @ 12:25:35
12064261h07-10-2020 @ 07:35:18
121643030m07-10-2020 @ 11:23:18
122643630m07-10-2020 @ 15:55:39
123644630m08-10-2020 @ 12:37:18
12464471h08-10-2020 @ 12:41:18
125645330m08-10-2020 @ 16:36:09
12664541h08-10-2020 @ 17:35:18
127645530m08-10-2020 @ 17:37:17
128645515m08-10-2020 @ 17:37:17
129645645m08-10-2020 @ 17:47:18
130647330m09-10-2020 @ 19:33:41
131647515m12-10-2020 @ 05:16:27
132647945m12-10-2020 @ 12:26:29
133648215m12-10-2020 @ 16:46:28
134648315m12-10-2020 @ 16:58:26
135648915m13-10-2020 @ 13:29:14
136649015m13-10-2020 @ 13:46:26
137650315m14-10-2020 @ 12:16:27
138651415m14-10-2020 @ 21:48:31
139651615m15-10-2020 @ 01:50:24
140651745m15-10-2020 @ 02:44:43
141652930m15-10-2020 @ 15:40:52
142653930m16-10-2020 @ 07:12:26
143655130m16-10-2020 @ 21:43:19
144655630m19-10-2020 @ 07:05:24
14565591h 30m19-10-2020 @ 08:49:25
14665601h19-10-2020 @ 08:51:23
147656330m19-10-2020 @ 09:59:24
148657415m19-10-2020 @ 20:29:32
149657515m19-10-2020 @ 20:55:24
150657745m20-10-2020 @ 00:33:24
151657815m20-10-2020 @ 00:35:24
152657915m20-10-2020 @ 00:35:26
153658130m20-10-2020 @ 09:25:24
154659115m20-10-2020 @ 15:20:24
155659115m20-10-2020 @ 15:20:24
156659345m20-10-2020 @ 16:11:24
Spiceworks Report
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
this will convert Col B to mins

Book1
ABCDE
1Ticket #Total Time Spent by UserCreate DateMins
255324h03-08-2020 @ 14:10:48240
3553830m03-08-2020 @ 22:24:5830
4555715m04-08-2020 @ 20:32:4515
5556845m05-08-2020 @ 09:10:4645
6556845m05-08-2020 @ 09:10:4645
7556930m05-08-2020 @ 11:16:4530
855851h06-08-2020 @ 12:00:4560
9559130m06-08-2020 @ 16:08:4630
1055921h06-08-2020 @ 16:42:5260
11559330m06-08-2020 @ 17:14:4530
12559515m06-08-2020 @ 21:22:5015
13559630m07-08-2020 @ 00:35:5130
14560315m07-08-2020 @ 20:55:2215
1556051h07-08-2020 @ 21:45:0360
16560730m07-08-2020 @ 23:51:1330
17561545m10-08-2020 @ 12:40:2745
18563915m12-08-2020 @ 05:25:5315
19566730m13-08-2020 @ 09:08:3030
20567015m13-08-2020 @ 11:24:2815
2156761h 30m13-08-2020 @ 15:40:2890
22567730m13-08-2020 @ 15:58:3130
Sheet3
Cell Formulas
RangeFormula
E2:E22E2=IFERROR(SUBSTITUTE(LEFT(B2,3),"h","")*60,0)+IFERROR(SUBSTITUTE(RIGHT(B2,3),"m","")*1,0)
 
Solution

darzu

New Member
Joined
Jul 13, 2020
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Thanks AlanY this solved my issue, you are great!
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,114,513
Messages
5,548,493
Members
410,840
Latest member
Kar3ousse
Top