Excel is applying automatic formatting when pasteing from a website

[Psychochook]

Hi,

I am pasteing some information into an excel spreadsheet as part of my number-crunching for horse-racing.

One of the cells of data has the time for the horses last race. It is in the format of mm:ss.ms. For example, 00:59.23 means that the horse completed the race in 59.23 seconds.

When I paste into Excel (2002) it appears fine when I look at the cell. But when I click on the cell, it appears as 12:00:59 AM in the formula bar. If I change that cell into a number, it changes to 0.000684027777777778. I know that this figure relates to the number of seconds in the day (eg 1 (day) = 86400 seconds )

I have tried setting custom formatting to the cell prior to pasteing (mm:ss.00) but it still appears as 12:00:59 AM in the formula bar and still converts to 0.000684027777777778 when I change the formatting. I have looked at Paste Special but that doesn't make any difference. It only gives me the options of pasteing as HTML, Unicode or as Text. If I paste using Unicode then it only pastes some information (that is of no use to me) and pasteing as text pastes all the information into one cell per row rather than individual cells. I have tried copying from the site using Internet Explorer and Firefox but that makes no difference.

The main problem with the thing is that I am trying to extrapolate the expected time that the horse will take to run the current race so I am wanting to use the value (59.23 seconds) in various formulas. With the decimal it is making it very difficult because every calculation that I am using around the timing I have to multiply or devide by 86400 to get it into a number that I can use.

I have a friend who copies from the same Website and pastes into Excel (Don't know what version he has) and he says that it isn't a problem for him. (I have not seen him do it or examined how the information appears on his PC because he lives is another state to me) This is causing me further headaches because I want to be able to share my spreadsheet with him but because my formulas have to include the multiplication or devision by 86400 they don't work for him. This makes me think that there must be some option that I can turn on or (more likely) off that will help me too. Has anybody got any ideas or suggestions because this is causing me some real headaches.

Thanks

Simon

[shg]

It's just a date to Excel -- in this case, 53 seconds after midnight on 01 Jan 1900.

After you import the data, change the format to [m]:ss.00

The main problem with the thing is that I am trying to extrapolate the expected time that the horse will take to run the current race so I am wanting to use the value (59.23 seconds) in various formulas.

That's what Excel was born to do. Scale it any way you wish, and set the same format.