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

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.
--
David Biddulph

"tman" wrote in message
...
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

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

--
Gary''s Student - gsnu200761


"tman" wrote:

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

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!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200761


"tman" wrote:

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

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

Even if you format the time column (column G) using
formatcellstime30: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
on the data - can't quite figure out what.



"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200761


"tman" wrote:

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

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.
--
David Biddulph

"tman" wrote in message
...
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!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200761


"tman" wrote:

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