hh:mm:ss data not padded with zeros and not sorting

say, I've Duration data in Excell looking like this:
10:34
1:23
08:34:01
and want to get it like this:
xx:xx:xx
so I can sort correctly by it? Tx....
shaunwinginAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
barry houdiniConnect With a Mentor Commented:
Well, you have the durations in column L on that example so the formula in M2 would be this

=TRIM(L2)/IF(ISNUMBER(SEARCH("*:*:*",L2)),1,60)

....copied down...

Essentially that is assuming that any value shown with a single colon like 1:21 is mins/secs and that only values shown with two colons are hrs/mins/secs, is that right?

see revised attachment

regards, barry
26834323v2.xls
0
 
patrickabCommented:
shaunwingin,

Select the column with the times in it, go to Format/Cells, select the Time format and select the first option in the list, press OK.

Patrick
0
 
shaunwinginAuthor Commented:
The data is left padded with spaces. This doesn't make difference even if pad.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
jppintoCommented:
Why is the data left padded with spaces?!?

You can remove the spaces by using Ctrl+H (Find and Replace), where you find a space and replace it with nothing. This will remove all the left spaces from your data. They you can formatt it like patrick told you.

jppinto
0
 
shaunwinginAuthor Commented:
Its imported from text file as a text field and this is its format. I've tried using trim function but time format doesn't help....
0
 
barry houdiniCommented:
sometimes the "spaces" are CHAR 160s...

If your data starts at A2 use this formula in B2 copied down

=TRIM(SUBSTITUTE(A2,CHAR(160),""))+0

format as hh:mm:ss

Does that work? If not can you post the workbook or at least a sample

regards, barry
0
 
shaunwinginAuthor Commented:
0
 
shaunwinginAuthor Commented:
I've attached the file - see the DurationHHMMSS column pls...
0
 
barry houdiniCommented:
I assume that K2 should be interpreted as 1 minute 21 seconds (not 1 hour and 21 minutes) so you can try this formula in M2 copied down

=TRIM(K2)/60

I then formatted as hh:mm:ss, see attached

If you want you can just custom format as mm:ss

regards, barry
26834323.xls
0
 
shaunwinginAuthor Commented:
Tx but some are hh:mm:SS. Data is mixed...
0
 
barry houdiniCommented:
OK try this formula

=TRIM(K2)/IF(ISNUMBER(SEARCH("*:*:*",K2)),1,60)

If that doesn't work can you attach a workbook which contains some examples where the time is greater than 1 hour?

regards, barry
0
 
shaunwinginAuthor Commented:
Afraid it didn't work as far as can tell.
Book3.xlsx
0
 
wchhCommented:
Use formula
=TIMEVALUE(L2)
0
 
shaunwinginAuthor Commented:
Barry,
That worked!

wchh: Formats as hh:mm! I believe solution is to replace SPACES only with formula of 00:00:00 and then all be ok.
Any idea how to do this?
Else Barry's is a good solution although a little more involved it would seem.
0
 
barry houdiniCommented:
The problem, always is to stop the values from formatting as hh:mm, so I don't see a way to do that simply here.....

One alternative (shorter) formula

=RIGHT("00:"&TRIM(L2),8)+0

I'm assuming that you won't have time durations greater than 99 hours.....

regards, barry
0
All Courses

From novice to tech pro — start learning today.