Please help on accumulating large time

AngelicaWan

New Member
Joined
Aug 20, 2015
Messages
8
Hi everyone,

I got an urgent question about accumulating large time. Please help!

Is there any way to sum up 3 time in each row from A3:C6? The data format is General which I copied from other data source. The time in this table means dd:hh:mm:ss. They could not be calculated even if I change the data format to custom -> dd:hh:mm:ss.

Thanks in advance!


[TABLE="width: 192"]


<colgroup><col style="width: 48pt;" span="4" width="64">
<tbody>[TR]

[TD="class: xl65, width: 64, bgcolor: white"]I[/TD]

[TD="class: xl65, width: 64, bgcolor: white"]II[/TD]

[TD="class: xl65, width: 64, bgcolor: white"]III[/TD]

[TD="class: xl65, width: 64, bgcolor: white"]Sum[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]17:04:02:02[/TD]

[TD="class: xl66, bgcolor: transparent"]00:00:24[/TD]

[TD="class: xl66, bgcolor: transparent"]2:23:27:40[/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]4:20:13:08[/TD]

[TD="class: xl68, bgcolor: transparent"]0:00:18[/TD]

[TD="class: xl66, bgcolor: transparent"]09:28:35[/TD]

[TD="class: xl67, bgcolor: yellow"]=A3+B3+C3[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]4:07:24:14[/TD]

[TD="class: xl66, bgcolor: transparent"]00:00:00[/TD]

[TD="class: xl66, bgcolor: transparent"]07:10:51[/TD]

[TD="class: xl67, bgcolor: yellow"]=A4+B4+C4[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]4:13:33:32[/TD]

[TD="class: xl66, bgcolor: transparent"]00:00:00[/TD]

[TD="class: xl66, bgcolor: transparent"]22:09:26[/TD]

[TD="class: xl67, bgcolor: yellow"]=A5+B5+C5[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]3:10:51:08[/TD]

[TD="class: xl66, bgcolor: transparent"]00:00:06[/TD]

[TD="class: xl66, bgcolor: transparent"]1:08:38:48[/TD]

[TD="class: xl67, bgcolor: yellow"]=A6+B6+C6[/TD]

[/TR]


</tbody>[/TABLE]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Is there any way to sum up 3 time in each row from A3:C6? The data format is General which I copied from other data source. The time in this table means dd:hh:mm:ss. They could not be calculated even if I change the data format to custom -> dd:hh:mm:ss.

Excel does not recognize "time" that is entered in the form d:hh:mm:ss. So it is treated as meaningless text.

Moreover, we cannot rely on the format d:hh:mm:ss because "d" represents day of the month, not number of days. By coincidence, "d" seems to work for 0 to 31 days because it represents the day in Jan 1900. (Zero is a special case.) But 32 represents 1 Feb 1900, so "d" is formatted as 1.

If you want to continue to enter "time" and to present the sum in the form d:hh:mm:ss, try the following:

1. Insert a row after row 2 (currently displays 17:04:02:02 etc).
2. Enter the following formulas. Note that A3 and D4 are array-entered: press ctrl+shift+Enter instead of just Enter.
Code:
A2: =INT(A3) & TEXT(A3,":hh:mm:ss")
A3: =SUM(IF(ISNUMBER(--A4:A7),--A4:A7,IFERROR(LEFT(A4:A7,FIND(":",A4:A7)-1)+MID(A4:A7,FIND(":",A4:A7)+1,99),"")))
D4: =INT(E4) & TEXT(E4,":hh:mm:ss")
E4: =SUM(IF(ISNUMBER(--A4:C4),--A4:C4,IFERROR(LEFT(A4:C4,FIND(":",A4:C4)-1)+MID(A4:C4,FIND(":",A4:C4)+1,99),"")))
3. Copy A2:A3 and paste into B2:B3 through C2:C3.
4. Copy D4:E4 and paste into D5:E5 through D7:D7.

You can hide row 3 and column E, if you wish. Alternatively, you can avoid the helper cells by duplicating the complex expressions in the formulas in row 2 and column D (gulp!)

The format of rows 2:3 and columns D:E does not matter.
 
Last edited:
Upvote 0
Errata....
Rich (BB code):
A2: =INT(A3) & TEXT(A3,":hh:mm:ss")
A3: =SUM(IF(ISNUMBER(--A4:A7),--A4:A7,IFERROR(LEFT(A4:A7,FIND(":",A4:A7)-1)+MID(A4:A7,FIND(":",A4:A7)+1,99),"")))
D4: =INT(E4) & TEXT(E4,":hh:mm:ss")
E4: =SUM(IF(ISNUMBER(--A4:C4),--A4:C4,IFERROR(LEFT(A4:C4,FIND(":",A4:C4)-1)+MID(A4:C4,FIND(":",A4:C4)+1,99),"")))
In case you missed my late edits, I simplified the formulas by eliminating INT and TIMEVALUE.
 
Upvote 0
Hi Joeu2004,

Thank you sooooooo much!!!

That formulas are so incredible!!!!! Very helpful!!!

I will do some research on the formulas you wrote this afternoon.

Thanks again for your detailed solution!

--Angelica
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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