Pasting Times in different formats

**adam2308** · 03-07-2012, 12:39 PM

I am trying to paste some swimming times from this website in to the attached workbook to get a list of swimming results.

My problem is the times (columns B and C) under 1 min are returning completely wrong results.

If you use the link above and set the criteria as:

Competition Period: 2012
Event: 100 BK LCM
Gender: Female
Cut/Standard: "All Times"
LCS: "All"
Age Range: All
Zone: All
Times to Include: All Times For Swimmer
Include times for USA Swimming Members only?: No
Max # of results to return?: 20
Sort By: Time

Then paste the results of rows 1-10 in to the Sheet 1 and you will see that the results of ranks 1-8 are now completely wrong whereas 9 and 10 correct.

Is there something I can do to keep all results columns B and C in the format 'mm:ss.00' regardless of whether or not it is under or above 1 minute.

**mahju** · 03-07-2012, 02:08 PM

Hi

Convert the red in to general formate and it will be ok

Regard

**adam2308** · 03-07-2012, 03:01 PM

I already tried but it doesn't do what I want. For example, if you change the format to general then take C2 which then becomes 59.36 (which is the correct value) but it is not comparable to C10.

So, if I do as you suggest and make rows general then I can't place formulas in other to looks for the fastest time in that column, which is the kind of thing I want to do. If I look for the quickest time (using the MIN formula) then it comes up as 01:00.03 (C9) when it should be 00:59.36 (c2).

Any other ideas? Please let me know if I am not being clear with anything.

Thanks.

**haru** · 03-07-2012, 05:13 PM

I made an adjustment to your adjusted time, it is still in custom format, but I noticed that for C2, you had 59.36, when it should be 00:59.36. That should fix your problem so you can find the minimum value. You can see the difference in values between column B and C.

**adam2308** · 03-07-2012, 05:29 PM

Hi haru,

What we now have in column C is exactly what we want, but how did you get these cells to display this way? How do you mean 'I made an adjustment to your adjusted time'?

Did you just type over the cells in column C?

Thanks,
Adam.

**haru** · 03-07-2012, 06:01 PM

Hi Adam,

What I meant was, for the times in red to be in the same format as that last 2 entries (rank 9 and 10), change the time format to general, then add "00:" (without quotes) to the beginning of the time. Afterwards, you can change the format back to custom (if you like) mm:ss.00 so it will not round up.

After adding the 00: to the time, the value in the cell will be comparable with the values in C10 and C11.

In column M, copy and paste the Alt Adj Time into it, format it to general, in N2 enter the formula

="00:"&M2

Copy N2 and paste down. Next you can just copy/paste values of Column N into the Alt Adj Time.

Hope that helps.

EDIT: just looking at the times you have in red, rank 1 with a time of 38 minutes seems much slower than 1 minute (rank 9). Which is the reason why you need to format the time correctly. Sorry if I'm not making myself clear.

**adam2308** · 03-07-2012, 06:41 PM

Thanks haru... that makes much more sense now and I think it should do the trick. I am way from my office now but will give it a try on my main document tomorrow and see if there any problems that I can't foresee at the minute. Looks good though! Will post again tomorrow. Thank you.