I have three columns in an Excel Worksheet.

Table Name
Time of Data Transfer
Size Data Transfer

Would anyone know how to format the Time of Data Transfer and Size of Data
Transfer column so that I could use the SUM function to get a total.
Here are the two columns:

4s 6 mb
1h28m52s 71.66 mb
1h20m57s 1110.83 mb
2h28m9s 699.39 mb
48s .19 mb
92s 3.61 mb
10s 18.53 mb
1h4m15s 584.68 mb
36s .36 mb
1.1s 4.21 mb
1.4s 13.27 mb
21s 21 mb
15s 3.07 mb
1s .20 mb
5m 111.45 mb
1s .02 mb
0

Thank you.

Hi,

If I understand correctly you want to add time and bytes. If you advise what
the answer to the sum below is I'm sure someone will come up with a solution

1h28m52s + 71.66 mb = ?

Mike


"ACarella" wrote:

I have three columns in an Excel Worksheet.

Table Name
Time of Data Transfer
Size Data Transfer

Would anyone know how to format the Time of Data Transfer and Size of Data
Transfer column so that I could use the SUM function to get a total.
Here are the two columns:

4s 6 mb
1h28m52s 71.66 mb
1h20m57s 1110.83 mb
2h28m9s 699.39 mb
48s .19 mb
92s 3.61 mb
10s 18.53 mb
1h4m15s 584.68 mb
36s .36 mb
1.1s 4.21 mb
1.4s 13.27 mb
21s 21 mb
15s 3.07 mb
1s .20 mb
5m 111.45 mb
1s .02 mb
0

Thank you.

I've got formula for you, however, for whatever reason that I cannot fathom,
the fractional seconds are not tabulating in my final formula.
The easy formula was for the mb:
=--TRIM(SUBSTITUTE(C2,"mb",""))

I did the formula for the time long and then tried to make it in one cell,
when I used the TIME function is when the fractional seconds disappeard.
If you want to do one column for hours, one for minutes, and one for
seconds, it is like this:
H: =IF(ISERROR(FIND("h",B2)),0,--LEFT(B2,FIND("h",B2)-1))
M:
=IF(ISERROR(FIND("h",B2)),IF(ISERROR(FIND("m",B2)) ,0,--LEFT(B2,FIND("m",B2)-1)),IF(ISERROR(FIND("m",B2,FIND("h",B2)+1)),0,--MID(B2,FIND("h",B2)+1,FIND("m",B2)-1-FIND("h",B2))))
S:
=IF(ISERROR(FIND("s",B2)),0,IF(ISERROR(FIND("m",B2 )),IF(ISERROR(FIND("h",B2)),--LEFT(B2,LEN(B2)-1),--MID(B2,FIND("h",B2)+1,LEN(B2)-FIND("h",B2)-1)),--MID(B2,FIND("m",B2)+1,LEN(B2)-FIND("m",B2)-1)))

This will properly display hours, minutes and seconds with fractional seconds.
My single formula encompassed the 3 previous formulae into the TIME
function, but as stated, dropped the fractional second (though it displayed
as .0).
=TIME(IF(ISERROR(FIND("h",B2)),0,--LEFT(B2,FIND("h",B2)-1)),IF(ISERROR(FIND("h",B2)),IF(ISERROR(FIND("m",B 2)),0,--LEFT(B2,FIND("m",B2)-1)),IF(ISERROR(FIND("m",B2,FIND("h",B2)+1)),0,--MID(B2,FIND("h",B2)+1,FIND("m",B2)-1-FIND("h",B2)))),IF(ISERROR(FIND("s",B2)),0,IF(ISER ROR(FIND("m",B2)),IF(ISERROR(FIND("h",B2)),--LEFT(B2,LEN(B2)-1),--MID(B2,FIND("h",B2)+1,LEN(B2)-FIND("h",B2)-1)),--MID(B2,FIND("m",B2)+1,LEN(B2)-FIND("m",B2)-1))))

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"ACarella" wrote:

I have three columns in an Excel Worksheet.

Table Name
Time of Data Transfer
Size Data Transfer

Would anyone know how to format the Time of Data Transfer and Size of Data
Transfer column so that I could use the SUM function to get a total.
Here are the two columns:

4s 6 mb
1h28m52s 71.66 mb
1h20m57s 1110.83 mb
2h28m9s 699.39 mb
48s .19 mb
92s 3.61 mb
10s 18.53 mb
1h4m15s 584.68 mb
36s .36 mb
1.1s 4.21 mb
1.4s 13.27 mb
21s 21 mb
15s 3.07 mb
1s .20 mb
5m 111.45 mb
1s .02 mb
0

Thank you.

Hi,

Here's another formula to convert the times, assume the time is in D5, and
format the cell to [h]:mm:ss (which is in the Custom area of the Number tab)

=TIMEVALUE(IF(ISERR(FIND("m",D5)),"0:","")&IF(ISER R(FIND("h",D5)),"0:","")&SUBSTITUTE(SUBSTITUTE(SUB STITUTE(D5,"h",":"),"m",":"),"s",""))

If this helps, please click the Yes button
--
Thanks,
Shane Devenshire


"ACarella" wrote:

I have three columns in an Excel Worksheet.

Table Name
Time of Data Transfer
Size Data Transfer

Would anyone know how to format the Time of Data Transfer and Size of Data
Transfer column so that I could use the SUM function to get a total.
Here are the two columns:

4s 6 mb
1h28m52s 71.66 mb
1h20m57s 1110.83 mb
2h28m9s 699.39 mb
48s .19 mb
92s 3.61 mb
10s 18.53 mb
1h4m15s 584.68 mb
36s .36 mb
1.1s 4.21 mb
1.4s 13.27 mb
21s 21 mb
15s 3.07 mb
1s .20 mb
5m 111.45 mb
1s .02 mb
0

Thank you.

Hi:
Thank you for responding.
No, I don't want to add time and bytes together. They are two separate
columns. I want to add all the time in the time column and get a total for
that column and then I want to add all the megabytes in another column and
get the total for that column.
Can you help?

"Mike H" wrote:

Hi,

If I understand correctly you want to add time and bytes. If you advise what
the answer to the sum below is I'm sure someone will come up with a solution

1h28m52s + 71.66 mb = ?

Mike


"ACarella" wrote:

I have three columns in an Excel Worksheet.

Table Name
Time of Data Transfer
Size Data Transfer

Would anyone know how to format the Time of Data Transfer and Size of Data
Transfer column so that I could use the SUM function to get a total.
Here are the two columns:

4s 6 mb
1h28m52s 71.66 mb
1h20m57s 1110.83 mb
2h28m9s 699.39 mb
48s .19 mb
92s 3.61 mb
10s 18.53 mb
1h4m15s 584.68 mb
36s .36 mb
1.1s 4.21 mb
1.4s 13.27 mb
21s 21 mb
15s 3.07 mb
1s .20 mb
5m 111.45 mb
1s .02 mb
0

Thank you.