Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2011-02-20
15
Medium Priority
?
208 Views
Last Modified: 2012-05-11
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....
0
Comment
Question by:shaunwingin
15 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34936837
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
 

Author Comment

by:shaunwingin
ID: 34936856
The data is left padded with spaces. This doesn't make difference even if pad.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34936868
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 

Author Comment

by:shaunwingin
ID: 34936886
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34936921
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
 

Author Comment

by:shaunwingin
ID: 34937312
0
 

Author Comment

by:shaunwingin
ID: 34937403
I've attached the file - see the DurationHHMMSS column pls...
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34937708
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
 

Author Comment

by:shaunwingin
ID: 34938097
Tx but some are hh:mm:SS. Data is mixed...
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34938130
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
 

Author Comment

by:shaunwingin
ID: 34938756
Afraid it didn't work as far as can tell.
Book3.xlsx
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 34938849
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
 
LVL 8

Expert Comment

by:wchh
ID: 34939538
Use formula
=TIMEVALUE(L2)
0
 

Author Comment

by:shaunwingin
ID: 34941620
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34948131
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This article presents several of my favorite code snippets.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question