Possible to sum up time in mm ss format?

charlesstricklin

charlesstricklin

Board Regular
Joined
May 6, 2013
Messages
88
Office Version
  1. 2010
Platform
  1. Windows
I'm most likely asking for the impossible, but is it possible to take a column of numbers showing time in a mm ss format and add them all up into an hh mm ss format? Text, obviously.

I'm using Excel 2010 on a Windows 10 PC.

35m 22s
25m 37s
30m 48s
2m 59s
18m 39s
19m 24s
38m 39s
21m 54s
5m 19s
17m 8s
26m 10s
2m 42s
23m 18s
13m 25s
31m 42s
30m 23s
2m 9s
37m 44s
38m 9s
2m 6s
6h 9m 37s
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
See if this works for you, the formula will need to be entered as an array formula using ctrl+shift+enter.

Book3
ABCDE
1OriginalTest1Test2
235m 22s00:35:2200:35:22
325m 37s00:25:3700:25:37
430m 48s00:30:4800:30:48
52m 59s00:02:5900:02:59
618m 39s00:18:3900:18:39
719m 24s00:19:2400:19:24
838m 39s00:38:3900:38:39
921m 54s00:21:5400:21:54
105m 19s00:05:1900:05:19
1117m 8s00:17:0800:17:08
1226m 10s00:26:1000:26:10
132m 42s00:02:4200:02:42
1423m 18s00:23:1800:23:18
1513m 25s00:13:2500:13:25
1631m 42s00:31:4200:31:42
1730m 23s00:30:2300:30:23
182m 9s00:02:0900:02:09
1937m 44s00:37:4400:37:44
2038m 9s00:38:0900:38:09
212m 6s00:02:0600:02:06
227h 3m 37s07:03:3707:03:37
23
Sheet2
Cell Formulas
RangeFormula
C2:C21C2=("00:"&SUBSTITUTE(SUBSTITUTE(A2,"s",""),"m ",":"))+0
D2:D21D2=("00:"&TEXT(R2,"00")&":"&TEXT(S2,"00"))+0
C22:D22C22=SUM(C2:C21)
A22A22=SUM(--("00:"&SUBSTITUTE(SUBSTITUTE(A2:A21,"s",""),"m ",":")))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
dave3009 said:
See if this works for you, the formula will need to be entered as an array formula using ctrl+shift+enter.

Book3
ABCDE
1OriginalTest1Test2
235m 22s00:35:2200:35:22
325m 37s00:25:3700:25:37
430m 48s00:30:4800:30:48
52m 59s00:02:5900:02:59
618m 39s00:18:3900:18:39
719m 24s00:19:2400:19:24
838m 39s00:38:3900:38:39
921m 54s00:21:5400:21:54
105m 19s00:05:1900:05:19
1117m 8s00:17:0800:17:08
1226m 10s00:26:1000:26:10
132m 42s00:02:4200:02:42
1423m 18s00:23:1800:23:18
1513m 25s00:13:2500:13:25
1631m 42s00:31:4200:31:42
1730m 23s00:30:2300:30:23
182m 9s00:02:0900:02:09
1937m 44s00:37:4400:37:44
2038m 9s00:38:0900:38:09
212m 6s00:02:0600:02:06
227h 3m 37s07:03:3707:03:37
23
Sheet2
Cell Formulas
RangeFormula
C2:C21C2=("00:"&SUBSTITUTE(SUBSTITUTE(A2,"s",""),"m ",":"))+0
D2:D21D2=("00:"&TEXT(R2,"00")&":"&TEXT(S2,"00"))+0
C22:D22C22=SUM(C2:C21)
A22A22=SUM(--("00:"&SUBSTITUTE(SUBSTITUTE(A2:A21,"s",""),"m ",":")))
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...

That seems to work, however, how do I format the 7:03:37 in A22 as 7h 3m 37s?
 
Upvote 0
Right click the cell >format cells...>time Type (select the format 13:30:55)
 
Upvote 0
charlesstricklin said:
I'm using Excel 2010 on a Windows 10 PC
Click to expand...

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
What am I doing wrong?

2018 Uber and Lyft Taxes and Expenses Spreadsheet.txt
C
1Time
25m 55s
310m 3s
413m 10s
525m 23s
650m 8s
75m 42s
89m 55s
922m 25s
1023m 18s
1132m 35s
1212m 37s
1326m 19s
147m 53s
158m 43s
1611m 17s
1713m 46s
1813m 7s
1920m 24s
207m 42s
2113m 49s
2215m 53s
234m 21s
2413m 47s
2517m 21s
2629m 53s
277m 12s
2827m 25s
295m 8s
304m 54s
3123m 11s
3226m 19s
337m 10s
3418m 32s
3520m 19s
3615m 55s
3715m 13s
3817m 32s
3922m 21s
4030m 5s
4119m 21s
4211m 35s
4314m 13s
4430m 20s
4515m 43s
467m 16s
477m 27s
4820m 20s
4919m 26s
5032m 27s
5110m 35s
5218m 13s
537m 39s
546m 4s
554m 4s
5618m 19s
5719m 54s
589m 55s
596m 55s
6017m 53s
6141m 31s
6220m 1s
6317m 40s
6411m 29s
6525m 23s
669m 9s
676m 49s
687m 43s
696m 23s
7023m 5s
7114m 44s
725m 19s
7318m 43s
7432m 39s
757m 49s
7631m 55s
776m 0s
7832m 56s
798m 43s
809m 31s
8111m 54s
8214m 25s
839m 23s
847m 31s
8512m 52s
8617m 23s
873m 22s
8826m 29s
8928m 0s
9020m 53s
9113m 16s
928m 41s
936m 42s
944m 19s
9518m 32s
968m 41s
9718m 3s
9816m 47s
9911m 47s
1006m 43s
10116m 30s
1023m 40s
1035m 48s
1041h 5m 20s
10520m 21s
1064m 43s
10722m 37s
10815m 36s
1097m 35s
11011m 27s
1113m 24s
11210m 47s
1139m 23s
1145m 24s
11514m 27s
11615m 31s
11714m 43s
11820m 52s
11913m 50s
12013m 41s
12114m 41s
12213m 52s
12319m 58s
12415m 23s
12510m 8s
1268m 50s
12753m 42s
12839m 8s
12916m 11s
13025m 54s
13128m 6s
13227m 0s
13327m 12s
13418m 55s
13511m 45s
13611m 29s
13712m 26s
13836m 41s
13921m 0s
14015m 52s
1419m 20s
1428m 40s
143#VALUE!
2018 Uber and Lyft Taxes and Ex
Cell Formulas
RangeFormula
C143C143=SUM(--("00:"&SUBSTITUTE(SUBSTITUTE(C1:C20,"s",""),"m ",":")))
 
Upvote 0
With older versions of Excel, array enter the formula CSE.
Custom format the result [h]:mm:ss

T202210a.xlsm
C
1Original
235m 22s
325m 37s
430m 48s
52m 59s
618m 39s
719m 24s
838m 39s
921m 54s
105m 19s
1117m 8s
1226m 10s
132m 42s
1423m 18s
1513m 25s
1631m 42s
1730m 23s
182m 9s
1937m 44s
2038m 9s
212m 6s
227:03:37
1e
Cell Formulas
RangeFormula
C22C22=SUM(--("00:"&SUBSTITUTE(SUBSTITUTE(A2:A21,"s",""),"m ",":")))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Dave Patton said:
With older versions of Excel, array enter the formula CSE.
Custom format the result [h]:mm:ss

T202210a.xlsm
C
1Original
235m 22s
325m 37s
430m 48s
52m 59s
618m 39s
719m 24s
838m 39s
921m 54s
105m 19s
1117m 8s
1226m 10s
132m 42s
1423m 18s
1513m 25s
1631m 42s
1730m 23s
182m 9s
1937m 44s
2038m 9s
212m 6s
227:03:37
1e
Cell Formulas
RangeFormula
C22C22=SUM(--("00:"&SUBSTITUTE(SUBSTITUTE(A2:A21,"s",""),"m ",":")))
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
🤦
 
Upvote 0
charlesstricklin said:
🤦
Click to expand...

I'm not doing it right, somehow. I'll include the entire worksheet. Maybe someone can walk me through the steps?

ABCD E
1DateMilesTime Earnings Tip
2Tuesday, July 19, 20222.175m 55s$ 3.41$ 2.00
3Thursday, July 21, 20224.0110m 3s$ 5.07$ 3.00
4Thursday, July 21, 20225.813m 10s$ 6.59$ 2.00
5Thursday, July 21, 202210.1625m 23s$ 10.87$ 6.41
6Thursday, July 21, 202215.7650m 8s$ 17.50$ 4.62
7Thursday, July 21, 20221.15m 42s$ 3.17$ 3.00
8Thursday, July 21, 20223.599m 55s$ 4.80$ 5.00
9Thursday, July 21, 20229.2622m 25s$ 9.92
10Friday, July 22, 20222.5123m 18s$ 5.84$ 5.00
11Friday, July 22, 202214.5832m 35s$ 14.53
12Friday, July 22, 20226.812m 37s$ 7.13$ 5.00
13Friday, July 22, 20229.8926m 19s$ 10.81
14Friday, July 22, 20224.377m 53s$ 5.02
15Friday, July 22, 20224.748m 43s$ 5.36
16Sunday, July 24, 20224.8411m 17s$ 5.75
17Monday, July 25, 20225.3813m 46s$ 6.40
18Monday, July 25, 20224.3413m 7s$ 5.67
19Monday, July 25, 20227.2120m 24s$ 8.39
20Monday, July 25, 20222.617m 42s$ 3.91
21Tuesday, July 26, 20224.1413m 49s$ 5.64
22Tuesday, July 26, 20222.2615m 53s$ 4.73
23Tuesday, July 26, 20220.84m 21s$ 3.17
24Tuesday, July 26, 20223.4713m 47s$ 5.21
25Wednesday, July 27, 20228.5117m 21s$ 8.81$ 5.00
26Wednesday, July 27, 202214.6529m 53s$ 14.23
27Wednesday, July 27, 20221.87m 12s$ 3.33
28Wednesday, July 27, 20228.0927m 25s$ 9.83
29Wednesday, July 27, 20221.525m 8s$ 3.17$ 3.00
30Wednesday, July 27, 20221.444m 54s$ 3.17$ 2.00
31Thursday, July 28, 20226.2923m 11s$ 8.17
32Thursday, July 28, 20229.826m 19s$ 10.75
33Thursday, July 28, 20222.197m 10s$ 3.58$ 2.00
34Friday, July 29, 202212.9318m 32s$ 11.72
35Friday, July 29, 20228.1820m 19s$ 8.98
36Friday, July 29, 20221.7815m 55s$ 4.44
37Friday, July 29, 202211.7315m 13s$ 10.54
38Friday, July 29, 20227.1217m 32s$ 7.97
39Friday, July 29, 202215.3522m 21s$ 13.70
40Friday, July 29, 202223.8630m 5s$ 19.98$ 6.98
41Friday, July 29, 20226.0419m 21s$ 7.52
42Friday, July 29, 20227.8111m 35s$ 7.64$ 6.00
43Friday, July 29, 20229.1114m 13s$ 8.78
44Saturday, July 30, 202212.7630m 20s$ 13.11
45Saturday, July 30, 202210.9915m 43s$ 10.14$ 1.90
46Saturday, July 30, 20222.467m 16s$ 3.76$ 2.00
47Saturday, July 30, 20222.487m 27s$ 3.79
48Saturday, July 30, 20229.3320m 20s$ 9.70$ 5.00
49Saturday, July 30, 202210.0119m 26s$ 10.01
50Saturday, July 30, 202222.0232m 27s$ 19.14
51Monday, August 1, 20222.310m 35s$ 4.08
52Monday, August 1, 202210.3718m 13s$ 10.07
53Monday, August 1, 20224.737m 39s$ 5.22
54Monday, August 1, 20224.496m 4s$ 4.86
55Monday, August 1, 20221.384m 4s$ 3.17
56Monday, August 1, 202210.5318m 19s$ 10.19
57Monday, August 1, 202210.2919m 54s$ 12.49
58Monday, August 1, 20222.869m 55s$ 7.34
59Monday, August 1, 20221.656m 55s$ 3.21$ 3.00
60Monday, August 1, 20221017m 53s$ 12.30
61Tuesday, August 2, 202216.6341m 31s$ 16.95$ 6.00
62Tuesday, August 2, 20224.1620m 1s$ 6.44
63Tuesday, August 2, 20224.3917m 40s$ 6.28
64Tuesday, August 2, 20223.7511m 29s$ 5.09
65Tuesday, August 2, 20229.8825m 23s$ 10.69
66Tuesday, August 2, 20221.99m 9s$ 3.65
67Tuesday, August 2, 20220.986m 49s$ 3.17
68Tuesday, August 2, 20223.427m 43s$ 4.41$ 5.00
69Tuesday, August 2, 20221.926m 23s$ 3.32
70Tuesday, August 2, 202212.2523m 5s$ 11.87
71Tuesday, August 2, 20222.0614m 44s$ 4.46
72Thursday, August 4, 20221.55m 19s$ 3.17$ 2.00
73Thursday, August 4, 202211.0818m 43s$ 10.58
74Thursday, August 4, 202214.4632m 39s$ 14.46
75Thursday, August 4, 20222.127m 49s$ 3.62
76Thursday, August 4, 202223.1331m 55s$ 19.77
77Thursday, August 4, 20221.576m 0s$ 3.17
78Thursday, August 4, 202211.4932m 56s$ 12.65$ 4.57
79Thursday, August 4, 20224.378m 43s$ 5.13
80Thursday, August 4, 20222.79m 31s$ 4.19
81Thursday, August 4, 20224.3811m 54s$ 5.54$ 3.00
82Thursday, August 4, 20224.7814m 25s$ 6.11
83Thursday, August 4, 20222.919m 23s$ 4.31
84Thursday, August 4, 20222.167m 31s$ 3.61
85Friday, August 5, 20224.7412m 52s$ 5.89$ 1.00
86Friday, August 5, 202210.4717m 23s$ 10.04$ 3.00
87Friday, August 5, 20220.33m 22s$ 3.17
88Friday, August 5, 202213.1326m 29s$ 12.85
89Friday, August 5, 202217.5728m 0s$ 15.81$ 5.40
90Friday, August 5, 202210.6520m 53s$ 10.59
91Friday, August 5, 20225.8413m 16s$ 6.62
92Friday, August 5, 20221.558m 41s$ 3.38$ 2.00
93Friday, August 5, 20221.666m 42s$ 5.94
94Friday, August 5, 20221.184m 19s$ 3.17
95Saturday, August 6, 20229.6318m 32s$ 9.66$ 5.00
96Saturday, August 6, 20223.98m 41s$ 4.83
97Saturday, August 6, 20229.1418m 3s$ 9.29
98Sunday, August 7, 20229.416m 47s$ 9.29$ 3.34
99Sunday, August 7, 20226.7411m 47s$ 7.00$ 2.00
100Sunday, August 7, 20222.616m 43s$ 3.77$ 3.00
101Sunday, August 7, 20228.1916m 30s$ 8.51
102Sunday, August 7, 20220.733m 40s$ 3.17
103Sunday, August 7, 20221.885m 48s$ 3.21
104Monday, August 8, 202227.861h 5m 20s$ 26.98
105Tuesday, August 9, 20223.2720m 21s$ 5.93
106Tuesday, August 9, 20221.434m 43s$ 3.17$ 2.00
107Tuesday, August 9, 202210.0722m 37s$ 10.45$ 3.00
108Tuesday, August 9, 20227.4815m 36s$ 7.94
109Tuesday, August 9, 20222.697m 35s$ 3.95
110Tuesday, August 9, 20225.4911m 27s$ 6.18$ 2.00
111Wednesday, August 10, 20220.833m 24s$ 3.17$ 1.00
112Wednesday, August 10, 20227.0410m 47s$ 6.30
113Wednesday, August 10, 20221.699m 23s$ 3.56
114Wednesday, August 10, 20221.435m 24s$ 3.17
115Friday, August 12, 20228.3914m 27s$ 7.71
116Saturday, August 13, 202211.5715m 31s$ 9.77$ 3.93
117Wednesday, August 17, 20229.3414m 43s$ 8.96
118Wednesday, August 17, 202211.5820m 52s$ 11.67$ 1.00
119Sunday, August 21, 20227.7913m 50s$ 7.00
120Sunday, August 21, 202211.6513m 41s$ 10.43
121Wednesday, August 24, 20223.7114m 41s$ 4.98$ 5.00
122Thursday, August 25, 20225.4113m 52s$ 6.97
123Thursday, August 25, 20225.1419m 58s$ 6.16
124Saturday, September 10, 20227.1215m 23s$ 6.35
125Thursday, September 29, 20224.8910m 8s$ 6.64$ 3.22
126Thursday, September 29, 20224.098m 50s$ 4.51$ 3.00
127Friday, September 30, 202226.8153m 42s$ 21.81
128Friday, September 30, 202227.4239m 8s$ 20.05$ 8.00
129Friday, September 30, 20227.716m 11s$ 7.33
130Friday, September 30, 202213.825m 54s$ 11.68
131Friday, September 30, 202218.7228m 6s$ 16.89
132Friday, September 30, 202214.8227m 0s$ 10.99
133Friday, September 30, 20227.6527m 12s$ 10.92
134Tuesday, October 4, 202213.2918m 55s$ 9.61
135Tuesday, October 4, 20222.211m 45s$ 4.33$ 5.00
136Tuesday, October 4, 20228.1811m 29s$ 9.87
137Tuesday, October 4, 202211.5512m 26s$ 9.08
138Tuesday, October 4, 202212.7636m 41s$ 12.50
139Tuesday, October 4, 20229.0921m 0s$ 7.57
140Friday, October 7, 20227.415m 52s$ 9.58
141Friday, October 7, 20223.189m 20s$ 7.03
142Friday, October 7, 20223.298m 40s$ 4.85
1431031.76$ 1,122.65$ 155.37


I'm trying to sum column C in C143. I think I'm doing CSE correctly but I'm obviously not.
 
Upvote 0
You must log in or register to reply here.

Similar threads

AndyTampa
  • Solved
InputBox to ask for date and only accept standard date formats
2 3
Replies
27
Views
1K
AndyTampa
AndyTampa
R
  • Solved
graph point labeling via macro on Mac version of Excel
Replies
7
Views
417
roscoe
R
AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
271
AOB
AOB
tezza
  • Question
Option explicit workbook help
Replies
3
Views
465
igold
igold
U
  • Question
conditional formatting in pivot table with slices
Replies
0
Views
569
uZoe
U

Forum statistics

Threads
1,215,073
Messages
6,122,970
Members
449,095
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top