pasting time from html

[DJH]

I am copying invoice data from HTML that I am pasting into excel. I
have multiple fields measured in hours/minutes/seconds. As an
example,

I copied the number 212:00:00. When I paste it into excel it is
recognized as 1/8/1900 8:00:00 PM. I want it recognized as
212:00:00. What are my options to convert?

 

[Gord Dibben]

I would copy all fields into Notepad or similar application first.

Then open in Excel and parse using Text Wizard.

In Step 3 format the column as Text.

Otherwise you could just paste one at a time into the formula bar after
formatting the cell as text.............or entering an apostrophe before pasting
to formula bar.


Gord Dibben MS Excel MVP

 

[Clif McIrvin]

But what if the OP wishes a time data type, not a text value?

 

[joeu2004]

I copied the number 212:00:00.  When I paste it into excel it is
recognized as 1/8/1900 8:00:00 PM.  I want it recognized as
212:00:00.   What are my options to convert?

Is being recognized as 212 hours and zero minutes and seconds. No
conversion is needed.

If you pasted into A1, try computing =A1*24 and format as General.
You will see 212. (Note: Excel might attempt to format the result as
time initially. Ignore it!)

Moreover, verify that the format in A1 is Custom [h]:mm:ss. If not,
make it so.

You are fooling yourself by looking at the cell value in the Formula
Bar. The Formula Bar has its own formatting rules. It displays
212:00:00 in that manner because time is stored as a fraction of 24-
hour days. So 212 is 8 "days" (8*24 = 192) plus 10 hours.

PS: Nonetheless, if you copy from a web page, I recommed that you
first paste into Notepad, then copy the Notepade text and paste into
Excel. Sometimes this is necessary in order to prevent Excel from
misinterpreting a web page "object".

 

[joeu2004]

Errata....

Is being recognized as 212 hours and zero minutes and seconds.  No
conversion is needed.

Minor typo.... That should be: "It is being recognized as 212
hours".

The Formula Bar has its own formatting rules.  [....]
So 212 is 8 "days" (8*24 = 192) plus 10 hours.

And the Formula Bar interprets that as a date. Dates are stored as
integers days since 12/31/1899 plus any fractional days (time). So 8
is interpreted as 1/8/1900, 8 days after 12/31/1899.

 

[Gord Dibben]

See joeu2004's response.


Gord

But what if the OP wishes a time data type, not a text value?

 

[Clif McIrvin]

Well, duh! Of course it's a date/time value .... else where would
1/8/1900 8:00:00 PM come from?!

Ah well, the memory of embarassment over "open mouth, insert foot"
events does fade with time <silly grin>.

Clif

 

[Gord Dibben]

My original response to OP would have produced Text so I can see why you posted
the query.

On re-reading OP's post I see a reference to wanting to "convert"

I should have assumed convert to time values, not text.


Gord

 

[Hans Terkelsen]

I am copying invoice data from HTML that I am pasting into excel. I
have multiple fields measured in hours/minutes/seconds. As an
example,

I copied the number 212:00:00. When I paste it into excel it is
recognized as 1/8/1900 8:00:00 PM. I want it recognized as
212:00:00. What are my options to convert?

DJH.
Try the userdefined format
[h]:mm:ss;
The final semicolon is there to make the format more permanent,
if you should happen to edit the cell.
Hans T.

 

[joeu2004]

Try the userdefined format
[h]:mm:ss;
The final semicolon is there to make the format more permanent,
if you should happen to edit the cell.

Fascinating! When you post something like, I think it is nice if you
post an example. But I can supply it....

In a new worksheet, set the format in A1 to Custom [h]:mm:ss (without
semicolon) and enter the date/time 1/1/2010 12:34. Note that XL2003
overrides our custom format :-(. But set the format in C2 [*] to
Custom [h]:mm:ss; (with semicolon) and enter 1/1/2010 12:34. Voila!
XL2003 honors our custom format, just as Hans says it would.

Well done, Hans! Can you explain why the semicolon makes a
difference? Is this just a workaround for an Excel defect? Or is
this all by design, that is some Excel principles?

Anyway, I'm glad you mentioned it. I will try to keep in mind for the
future.

 

[joeu2004]

But set the format in C2 [*]

I meant to explain why I chose C2 instead of A2 or B1, i.e. cells
adjacent to A1.

Sometimes Excel applies some heuristics to adjacent cells. By
choosing a non-adjacent in another row/column, I am more comfortable
that Excel's heuristics won't get in the way.

 

[Hans Terkelsen]

But set the format in C2 [*]

I meant to explain why I chose C2 instead of A2 or B1, i.e. cells
adjacent to A1.

Sometimes Excel applies some heuristics to adjacent cells. By
choosing a non-adjacent in another row/column, I am more comfortable
that Excel's heuristics won't get in the way.

Yes, Joeu.
And a calculation often inherits dateformats from precedents in the calculation.
Which is not what we want if we make a subtraction of two dates.
Then a detailed userformat like General;General seems to tell Excel
that we really mean it
Hans T.

 

[Hans Terkelsen]

"Hans Terkelsen" <dk> wrote in message ....
Yes, Joeu.
And a calculation often inherits dateformats from precedents in the calculation.
Which is not what we want if we make a subtraction of two dates.
Then a detailed userformat like General;General seems to tell Excel
that we really mean it
Hans T.

General;-General would be better.
Hans T.