# Thread: Cell format for swimming times

1. ## Cell format for swimming times

1:00.07Y Data coming from swimteam software.
1:02.23Y Want to sort from lowest to highest.
1:03.80Y Always puts the one minute times above the
1:04.37Y times of less than a minute.
1:04.87Y Any sugesstions.
1:06.83Y Have tried combo's of mm:ss.00 under format.
1:07.59Y
1:07.92Y
1:10.75Y THANKS
1:13.91Y
1:14.94Y
34.68Y
35.25Y
36.24Y
36.35Y
37.02Y
37.09Y
37.74Y
38.36Y
38.64Y
38.82Y

2. ## Re: Cell format for swimming times

Jim:
There may be formats that you can set up to handle this but I don't know of any. One problem is that the data are in two different formats. You can use the following
equation to convert the entries to numbers:
=IF(LEN(F25)<=5,F25/60,F25*24*60) and then copy the converted data on top of itself or to another column using Paste, Special, Values (from the EDIT dropdown menu) and then sort the new column..

3. ## Re: Cell format for swimming times

There are two things that you could do. First, with that "Y" on the end, those are being stored as sstrings not numbers. To get that to sort into the correct order, you would have to put a "0:" (without the quotes) in front of all of the times that are less than one minute. The other thing that you could do would be to convert those times to numeric time values. To do that you would have to put a "0:" (without the quotes) in front of all the times that are greater than one minute, and a "0:0:" (again without the quotes) in front of the values less than one minute. Then you would have to remove the "Y" from the end (if it is important and can be other letters, it could be put in another column). You can then select those values and in the Data menu select "Text to Columns" and just use the defaults in the wizard to convert the strings to time values.

4. ## Re: Cell format for swimming times

If the data includes the "Y", you should get rid of it with Find, Replace with a "" value. (If it is required you could keep a copy of course)

If your subsequent data is stored in A2 try the following formula in B2

=(IF(LEN(A2)>5,A2,TIMEVALUE("0:"&A2)))

It seemed to work for me, and the subsequent results sorted as you want them.

Regards,

Andrew

5. ## Re: Cell format for swimming times

I would just add 1 thing to what Andrew said (or perhaps it's a slight modification of what he said).

After getting rid of the "Y" with Replace and adding the "0:" to the sub-minute times to get actual numeric time values, you can still have the "Y" appear in the cell with the times if that's needed for some reason. The key word is "appear". It would NOT be part of the value of the cell or else, as stated, the value is a string and not a number.

After you've done what has been suggested, select the resulting cells. Format them by Format | Cells with the number tab. Select the last item which is custom format. A custom format will be preselected (General) but you want to define a new one. Enter the following format in the box at the top where you can type as follows:
mm:ss.0"Y"

Here you do want to include the quotes. What this does is keep the value but add a Y to what appears in the cell. Think of it as clicking the \$ formatting on the toolbar when you want a number to appear as dollars.

But let me also suggest something else. There is no reason why cells have to be numeric to be sorted as has been implied. Excel will sort text strings using the order associated with the code of the character. So you would still need to add the "0:" as was suggested to the sub-minute times. This is so that a 36.24 (36.24 seconds) sorts in ascending order ahead of a 1:00.07 (1+ minute) time. But with that task done, Excel will be perfectly happy to sort the list as strings with "0:36.24Y" sorting ahead of "1:00.07Y". As strings, you can not do mathematical operations like adding 2 of these cells but you can sort them.

Enough of sorting. It's Sunday morning and time to go to breakfast.

Fred

6. ## Re: Cell format for swimming times

In fact there is no need to replace te Y at all if you are happy with text and will perform no calculations with the data. The following formula would do the job :-

=IF(LEN(L3)>6,L3,"0:"&L3)

However it might be nice to show the margins between each time. How far can you swim in 34 seconds anyway ? !!!

Andrew

7. ## Re: Cell format for swimming times

Andrew,

We came to the same conclusion about not needing to convert to numeric values. I mentioned that as an after-thought at the end of my previous email.

But being able to do mathematical operations to, for example, find the margins of 2 successive times is useful. Not part of the original request but useful.

I'm not much of a swimmer and haven't swam in some time. I jog. In 34 seconds, on a good day and with the wind at my back, I can jog .1 mile. Don't ask how long I can keep that up for (not much). I like the long slow runs.[img]/w3timages/icons/grin.gif[/img]

fred

8. ## Re: Cell format for swimming times

That sounds like a 5.7min mile to me -- which, in my opinion, has crossed the line from jogging to running. And at a pretty decent pace to boot!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•