colon as an operator (used twice in a string)

T

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
 
D

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.
 
G

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
 
T

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!
 
D

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.
 
Top