• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Input masks in Excel

I need to input times, either in minute/seconds/tenths or just seconds/tenths and then rank those times fastest to slowest.

Is there a mask I can use so that I don't need to repeatedly type a colon or a point, but which will still allow me to sort entries?
0
kiwi246
Asked:
kiwi246
1 Solution
 
sebastienmCommented:
Directly, without using vba code.. i don't know, but if adding a column is ok.

Assuming you enter as 66.2 for 1 mn 6 s 2 tenth
Say you type in column A, starting in A2
- Format column B as (in Format dialog box)
   Category:   Time
   Type    :   30:55.7
 equivalent to
   Category: Custom
   Type    : mm:ss.0
- In B2, enter : =A2 * "00:00:01"
- Copy formula B2 down the <future> data

Now you can sort on column B.

Maybe someone will find a better way.

Regards,
Sebastien
0
 
Steve KnightIT ConsultancyCommented:
That works well, I'd try that if you need it to display as time aswell.  You can always just sort on column A though if you've got 66.2 type entries :-)

Other than that its VBA like Sebastien said where you can have it change what you type into something else but that is probably over complicating things for this one.

You could also enter as:

125.4
to mean 1 minute 25.4 seconds using this formula:
=INT(A1/100) * 60 + A1-(INT(A1/100)*100)

or 1254
to mean 1 minute 25.4 seconds using this formula:
=INT(A2/1000) * 60 + (A2-(INT(A2/1000)*1000))/10

but unless you can't just enter in seconds I'd use sebastien's method!

Steve
0
 
bruintjeCommented:
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in the next round.  I would appreciate any comments by the experts that would help me in making a recommendation.


It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
EE Cleanup Volunteer
0
 
dbase118Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ \ Answered by Sebastienm
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

dbase118
EE Cleanup Volunteer
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now