Solved

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

Posted on 2011-02-20
15
204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

626 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