Convert range values to correct "hh:mm:ss" format.

[deekaye]

When exporting data from another application as an excel worksheet I
end up with it in the following format.

Stat 1 Stat 2
Person1 01:16:00 01:09:25
Person2 :18 :49:12

I want to macro that will convert all the data in the range to
"hh:mm:ss".
Just applying data type cell formatting as hh:mm:ss does not fix the
values for Person2 and seems excel just treats it as text.
What is the most efficient way to do a check of all the data and then
fix the value of the data that is not in the correct format to
hh:mm:ss.

Thanks.

 

[deekaye]

When exporting data from another application as an excel worksheet I
end up with it in the following format.

Stat 1 Stat 2
Person1 01:16:00 01:09:25
Person2 :18 :49:12

I want to macro that will convert all the data in the range to
"hh:mm:ss".
Just applying data type cell formatting as hh:mm:ss does not fix the
values for Person2 and seems excel just treats it as text.
What is the most efficient way to do a check of all the data and then
fix the value of the data that is not in the correct format to
hh:mm:ss.

Thanks.

Just a follow up,
The data for Person1, Stat1 in the original application was 1 minute
and 16 secs - 01:16 and this is in "mm:ss" but when taken to excel it
becomes 01:16:00 in "hh:mm:ss" instead of the "00:01:16" format that I
need it to be in.

 

[Dave Peterson]

I would insert two helper columns to the right of each original column.

I used column C and Column E (B and D were the original columns).

I put this in C1:

=IF(ISNUMBER(B1),B1/60,IF((LEN(B1)-LEN(SUBSTITUTE(B1,":","")))=1,
--("0"&B1)/60,--("0"&B1)))
(all one cell)

Then I copied it to E1.

And dragged C1 done and E1 down as far as necessary.

I ended up with this (all formatted as hh:mm:ss):

A B C D E
Person1 01:16:00 00:01:16 01:09:25 00:01:09
Person2 :18 00:00:18 :49:12 00:49:12

Did :18 mean 18 seconds?
Did :49:12 mean 49 min 12 secs?

 

[deekaye]

Thanks Dave,

It work almost fine, just that I should have clarified that the data
Person1, Stat2 01:09:25 when taken to excel is actually in the correct
format (hh:mm:ss) and does not need to be converted to anything, it
just needs to be left alone.

How can I tweak the formula to include this possibility.

thanks.

 

[Dave Peterson]

You'd have to use a different formula in column in E1.

=IF(ISNUMBER(d1),d1,IF((LEN(d1)-LEN(SUBSTITUTE(d1,":","")))=1,
--("0"&d1)/60,--("0"&d1)))

But I'm not sure what :49:12 meant.

So that last portion is still a guess.