Solved

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

Posted on 2011-02-20
15
199 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now