Adding up timings in excel

[Paul Naylor]

Hi All,

Extracted data ( times ) from a text file , but although I've changed the format to time (hh:mm:ss) and adjusted the format of the sum to [hh]:mm:ss it won't give me the correct figure . Below is one of the columns (P5:P73). Formula for Sum ...=Sum(P5:P73) . Figure should he at least 13 and coming out at 3:10:42( figure in last cell). Anyone any idea what the issue is ?

:02:02

:02:53

:00:35

:03:16

:02:41

:04:11

:31:16

:02:10

:01:41

:03:30

:02:42

:30:13

:03:10

:02:58

:07:56

:02:42

:22:18

:02:05

:50:24

:04:26

:34:57

:03:58

:04:34

:04:16

:08:06

01:32:14

:05:58

:04:07

:25:07

:22:25

:14:34

:05:02

:25:59

:19:25

:00:12

:07:17

:15:49

:51:12

:38:03

:05:50

:05:33

:26:43

:00:00

:00:00

:01:43

:00:00

:01:43

:08:37

:02:09

:09:44

01:38:28

:02:28

:00:00

:01:50

:13:26

:00:00

:00:00

:00:00

:00:00

:00:00

:00:53

:02:26

:01:43

:01:52

:02:26

:03:36

:00:02

:02:14

:05:49

3:10:42

 

[MARK858]

Only 2 are real times (01:32:14 and 01:38:28), the rest are text. You need to append 00 at the front of the other values.

Book1
	P	Q	R
5	:02:02		00:02:02
6	:02:53		00:02:53
7	:00:35		00:00:35
8	:03:16		00:03:16
9	:02:41		00:02:41
10	:04:11		00:04:11
11	:31:16		00:31:16
12	:02:10		00:02:10
13	:01:41		00:01:41
14	:03:30		00:03:30
15	:02:42		00:02:42
16	:30:13		00:30:13
17	:03:10		00:03:10
18	:02:58		00:02:58
19	:07:56		00:07:56
20	:02:42		00:02:42
21	:22:18		00:22:18
22	:02:05		00:02:05
23	:50:24		00:50:24
24	:04:26		00:04:26
25	:34:57		00:34:57
26	:03:58		00:03:58
27	:04:34		00:04:34
28	:04:16		00:04:16
29	:08:06		00:08:06
30	01:32:14		01:32:14
31	:05:58		00:05:58
32	:04:07		00:04:07
33	:25:07		00:25:07
34	:22:25		00:22:25
35	:14:34		00:14:34
36	:05:02		00:05:02
37	:25:59		00:25:59
38	:19:25		00:19:25
39	:00:12		00:00:12
40	:07:17		00:07:17
41	:15:49		00:15:49
42	:51:12		00:51:12
43	:38:03		00:38:03
44	:05:50		00:05:50
45	:05:33		00:05:33
46	:26:43		00:26:43
47	:00:00		00:00:00
48	:00:00		00:00:00
49	:01:43		00:01:43
50	:00:00		00:00:00
51	:01:43		00:01:43
52	:08:37		00:08:37
53	:02:09		00:02:09
54	:09:44		00:09:44
55	01:38:28		01:38:28
56	:02:28		00:02:28
57	:00:00		00:00:00
58	:01:50		00:01:50
59	:13:26		00:13:26
60	:00:00		00:00:00
61	:00:00		00:00:00
62	:00:00		00:00:00
63	:00:00		00:00:00
64	:00:00		00:00:00
65	:00:53		00:00:53
66	:02:26		00:02:26
67	:01:43		00:01:43
68	:01:52		00:01:52
69	:02:26		00:02:26
70	:03:36		00:03:36
71	:00:02		00:00:02
72	:02:14		00:02:14
73	:05:49		00:05:49
74	3:10:42		12:45:39
Sheet1

Cell Formulas
Range	Formula
P74,R74	P74	=SUM(P5:P73)
R5:R73	R5	=IF(LEN(P5)=6, "00"&P5,P5)+0