Problem with 00:00:00:00 time format from cvs file

[totomo]

Hi all,

Sorry after searching a few days i can't find a solution to my problem.
I import in excel a cvs file, and I just want to add times ( elapsed times )

The format of the data is 00:00:00:00 as days:hours:mins:secs
I understood that dd:hh:mm:ss is not a valid format for this ( day bein the day of the month). I tried [h]:mm:ss and a lot of others time formats, but I can't find a way to do a SUM of the cells

Is there a simple way of doin' this ( the same way as hh:mm:ss cells )

Thx a lot,

 

[totomo]

I found a way by striping the day and the rest, and to SUM them seperatly
is there any elegant way to do it?

 

[shg]

One way:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]17:07:22:30[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]24:04:17:44[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]41:09:37:47[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]30:08:50:54[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]42:22:17:50[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]20:03:40:55[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]32:11:31:10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]14:20:51:02[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]02:21:19:04[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#E5E5E5"]

226.5756481​

[/td][td]A11: {=SUM(--LEFT(A2:A10, 2), --MID(A2:A10, 4, 8))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#E5E5E5"]226:13:48:56[/td][td]A12: =INT(A11) & TEXT(MOD(A11, 1), "\:hh:mm:ss")[/td][/tr]
[/table]

 

[totomo]

thank you, i think i'm gonna do this way
too bad there's no "easy" way

 

[shg]

You're welcome.

This is a little more robust:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]17:07:22:30[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]24:04:17:44[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]41:09:37:47[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]30:08:50:54[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]42:22:17:50[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]20:03:40:55[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]32:11:31:10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]14:20:51:02[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]02:21:19:04[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#E5E5E5"]

226.5756481​

[/td][td]A11: {=SUM(--LEFT(A2:A10, FIND(":", A2:A10) - 1), --RIGHT(A2:A10, 8))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#E5E5E5"]226:13:48:56[/td][td]A12: =INT(A11) & TEXT(MOD(A11, 1), "\:hh:mm:ss")[/td][/tr]
[/table]

 

[totomo]

i'm trying to do it on a column (column A instead of A2:A10 ) but can't find a way to do it