Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-20
15
Medium Priority
?
205 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

660 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