colon as an operator (used twice in a string)

[tman]

Greetings - why does Excel automatically interpret 1:53:822 (1 min 53.822
secs) as 0.087986111, yet 1:53:022 is not changed at all? Is there a way I
can calculate backwards the 0.0879... result into the original 1:53:822?

(These are times I am copying/pasting from a website into a spreadsheet.
There are dozens of them, and I'd rather not rekey them).

I spent a couple hours messing around with time/date formatting, reverse
engineering the calculation, etc., and have not cracked the code. And I can't
find anything out about the colon as an operator that would indicate what is
going on.

Any thoughts??? Thanks for any help! And happy holidays!!

-Tom

 

[David Biddulph]

If you want 1 minute 53.822 seconds, enter it as 1:53.822, with a decimal
point as the decimal separator, not an extra colon.
The colon is used to separate hours from minutes and minutes from seconds.
1:53:822 is 1 hours, 53 minutes, and 822 seconds, which is 0.087986111 of a
day, and Excel dates and times are stored as fractions of a day.

 

[Gary''s Student]

Excel thinks you have entered:
1 hour + 53 minutes + 822 seconds

Which is the same as:
2 hours + 6 minutes + 42 seconds

try entering it as:
0:1:53.822

 

[tman]

Well that makes sense - thanks for explaining it.

Unfortunately, that won't quite solve the problem because I'm
copying/pasting this info from a webpage, so I can't just change the second
colon to a period/decimal point. As soon as I paste Excel automatically
performs the calc you describe below. Tried formatting the cells as text but
that didn't stop it. Is there a way to "turn off" calculation, so I could go
in and find/replace the second colon with a period?

Thanks!

 

[tman]

PS - here is a sample data set:
http://www.afmracing.org/downloads/results/2007/inf-0407.html

Even if you format the time column (column G) using
format>cells>time>30:15:25 (or whatever that number was - third from bottom
of list), and then do the copy/paste, Excel preserves the dual colon
formatting, but still changes the times - it's doing some kind of time calc

 

[David Biddulph]

Don't paste into Excel directly, but put the data into a text file. You can
then either open the txt file, or use Data/ Import External Data, to pull in
the data from the txt file. Going through that route you can specify the
format of the column as text and it will stay that way.