How to add two columns values which have time in t

Question

I have few columns in an excel sheet for which I need to do a summation. The cell contains data in this format: hh:mm.

I need the summation in the cell below them. I'm not able to identify how should I format the cells and what to write in the formula!

I have MSEXCEL 2007.

EDIT: Included DATA.

Say I have this much data (Sorry looks weired, please consider spaces as next cell):

3:20 1:30 2:20 1:12 1:00 1:00 1:40 7:55 0:15 0:20 1:20 1:30 3:20 0:15 1:20 1:17 0:33 1:20 2:25 2:54 0:19 0:10 1:35 1:45 2:15 0:20 0:45 0:17 4:47 3:00 1:00 5:30 0:30 0:05 1:50 4:05 2:18 1:15 3:05 0:30 0:10 3:00 0:45 3:00 5:05 6:00 1:20 2:20 0:13 0:20 5:20 5:20 0:20 0:09 6:55 0:20 3:11 2:30 7:00 7:00 0:20 0:14 8:46 0:15 5:10 0:20 0:25 2:05 0:10 3:17 2:59 0:25 1:35 0:15 1:05 0:35 1:55 2:40 0:30 2:05 0:20 0:17 3:20 1:15 4:30 1:20 0:10 1:35 1:00 1:55 0:50 0:15 1:10 0:10 1:45 1:00 0:50 1:10 1:25 1:05 1:50 0:12 0:30 1:15 0:45 3:30 1:35 0:15 0:10 0:50 0:30 7:10 0:18 1:15 0:20 0:25 1:15 3:00 1:00 0:25 3:39 0:13 2:47 0:25 1:05 0:26 0:15 0:25 3:14 2:45 0:40 2:20 0:38 0:12 2:37 2:03 6:55 0:18 4:30 0:10 1:00 3:45 2:40 0:10 0:29 1:18 0:50 0:56 5:44 0:14 0:30 0:15 1:00 5:25 2:05 0:55 0:09 4:05 0:35 0:30 0:10 0:15 3:08 2:53 2:43 0:31 1:25 0:10 1:43 1:00 1:20 1:20 0:15 1:30 5:05 2:55 0:20 0:10 4:25 2:40 0:08 0:13 0:12 9:50 0:15 0:25 0:10 4:30 0:35 0:15 0:10 1:20 1:00 3:09 2:41 7:34 0:11 1:05 0:20 0:10 1:59 0:20 2:02 0:38 0:35 2:15 0:22 0:12 4:37 0:35 2:10 0:10 1:54 2:50 0:40 3:12 0:09 0:09 1:21 0:35 3:30 3:00 0:25 0:29 0:16 7:21 0:05 4:52 2:30 0:45 0:25 0:40 0:20 3:48 0:37 2:53 0:15 0:50 1:40 0:13 2:11 1:30 0:50 0:38 0:05 1:55 0:39 2:02 0:43 1:56 0:10 0:15 4:29 0:40 0:35 0:15 0:44 2:17 0:14 0:08 3:09 0:49 0:58 2:21 1:38 2:02 1:23 0:49 2:44 0:16 8:28 0:17 7:37 0:25 7:34 0:23 0:12 4:32 0:50 0:30 2:05 0:50 7:39 1:10 1:30 0:24 1:40 0:05 2:55 0:10 7:48 0:10 0:10 0:14 2:37 2:04 1:01 2:35 0:06 6:42 0:20 0:20 0:13 5:53 0:27 2:55 0:10 0:28 0:11 1:46 1:55 0:59 4:40 0:08 6:19 0:18 0:45 3:18 0:50 2:20 0:20 0:10 6:10 0:21 0:09 0:20 5:52 0:23 1:46 0:10 7:03 0:20 3:55 1:02 1:52 0:28 2:54 0:17 0:11 1:45 0:10 5:52 0:50 1:20 0:10 0:20 3:36 0:55 0:25 0:25 0:14 0:41 2:58 0:10 0:10 1:03 0:35 1:10 1:33 0:30 4:10 0:18 6:03 0:26 2:57 0:53 6:58 0:32 1:57 0:25 5:09 3:16 6:08 2:20 3:59 4:17 5:15 0:31 2:40 5:34 0:58 2:36 6:13 0:26 2:55 5:48 6:40 1:30 0:30 1:14 0:50 0:20 0:20 0:25 2:05 5:04 0:35 2:09 1:02 0:45 5:22 0:30 7:55 8:11 5:37 3:03 3:34 6:50 2:55 2:30 0:40 0:31 3:54 7:05 0:27 1:08 3:29 0:29 2:56 0:20 0:42 0:05 6:00 0:37 0:28 2:10 0:40 0:25 2:45 0:30 3:37 0:55 1:40 0:40 3:05 5:42 0:10 1:15 8:01 6:50 7:21 6:30 7:13 7:00 6:25 1:30 4:11 0:37 1:10 9:22 6:40 9:15 0:10 0:45 0:45 0:30 1:00 1:15 1:50 0:30 2:15 0:25 0:50 1:10 3:03 1:10 0:55 0:20 2:25 1:10 0:10 0:10 0:50 2:50 0:15 2:50 1:00 0:45 1:00 1:15 4:15 0:20 0:20 1:20 0:00 0:10 4:55 0:35 1:40 11:15 1:03 3:55 0:20 0:18 0:07 0:10 1:40 0:59 2:20 0:10 0:50 0:30 0:17 1:56 1:40 0:55 0:30 0:57 0:45 3:53 0:32 0:25 0:35 0:05 3:38 0:32 2:28 0:38 2:39 0:20 0:40 1:20 0:20 0:07 2:46 0:39 1:15 1:24 2:53 1:35 0:35 2:40 0:25 0:47 0:10 1:35 1:08 1:12 0:16 1:18 0:26 0:25 0:44 0:11 0:10 0:07 0:15 0:14 0:17 4:40 0:39 0:25 0:50 0:20 6:15 1:37 0:17 3:45 0:38 0:30 1:20 5:45 0:40 0:50 2:15 0:48 0:27 0:08 0:12 1:45 0:25 0:17 0:07 0:06 0:35 5:53 0:15 0:47 6:55 1:20 0:38 1:00 0:15 3:55 1:35 0:25 5:27 0:28 0:45 0:05 3:38 0:55 0:55 2:00 0:13 0:35 5:10 0:35 0:20 0:10 0:50 0:35 2:37 3:25 0:30 0:45 0:35 0:15 1:28 1:00 0:15 0:35 0:50 1:50 0:10 0:45 0:50 1:20 0:45 2:05 0:30 1:04 0:47 0:40 0:28 0:27 1:30 1:05 0:30 0:56 0:15 0:35 1:00 1:30 2:18 0:55 1:13 0:34 0:09 3:45 0:40 1:00 0:40 0:30 2:15 1:55 3:00 2:13 0:25 0:34 0:49 2:45 1:48 0:42 2:10 0:30 0:30 0:10 3:35 0:30 1:00 1:15 3:09 3:55 4:20 4:10 1:35 1:20 0:32 0:55 1:05 2:29 0:35 0:25 1:02 1:10 1:55 0:55 0:45 0:12 2:03 0:20 0:30 1:37 0:43 0:40 5:35 3:41 0:44 0:28 1:10 0:22 0:25 0:07 3:25 1:59 0:45 1:59 0:25 1:47 0:23 3:36 2:06 1:43 0:35 0:45 0:30 2:04 2:25 5:10 0:45 1:30 4:20 1:00 0:30 0:50 0:45 0:29 1:10 1:20 4:30 0:45 1:15 3:25 0:30 3:40 0:35 0:29 1:20 0:17 4:39 0:31 0:55 0:20 0:20 6:50 3:00 0:30 1:47 0:15 0:35 0:10 2:45 0:40 0:10 0:30 1:58 0:25 0:18 0:50 0:23 0:16 3:04 0:15 0:55 0:42 3:17 0:20 0:15 0:51 0:41 4:35 3:35 0:45 0:20 0:15 0:45 0:55 1:05 2:50 2:15 0:30 2:10 0:25 0:10 2:35 1:02 0:40 0:25 2:25 0:15 19:08 0:30 1:35 2:50 3:39 7:18 7:46 0:51 0:22 4:17 0:39 1:12 0:17 0:15 0:10 0:21 1:19 5:19 0:25 2:14 0:18 1:45 0:32 0:55 1:25 0:35

And the normal SUM I'm getting is: 00:36

What am I missing?

Any help appreciated.

Answer 1

Sum them as normal =SUM(A2:A3) and set the cell format mask for the result cell as d hh:mm or [h]:mm

Answer 2

Manish, please be aware that even though the cell shows “1416:36” due to the format “[h]:mm” the actual value of the cell remains “59.025”. Which would be great if the purpose of the calculation is only to inform users the total of hours and minutes. However if this total is going to be further used for other calculations or to build a Chart you should then use any of the two formulas included below to obtain either the:

1. Total number of hours and minutes, calculating minutes as a decimal of one hour i.e.: 36 minutes = 0.6 hours (value 1416.6)

   =SUM(INT(SUM($A$7:$A$813)*24),MOD(SUM($A$7:$A$813)*24,1))

2. Total number of hours and minutes (value 1416.36)

   =SUM(INT(SUM($A$7:$A$813)*24),MOD(SUM($A$7:$A$813)*24,1)*60/100)