pasting time from html

  • Thread starter Thread starter DJH
  • Start date Start date
D

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?
 
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
 
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?
Click to expand...

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".
 
Errata....

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

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.
Click to expand...

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.
 
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
 
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
 
DJH said:
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?
Click to expand...

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.
 
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.
Click to expand...

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.
 
But set the format in C2 [*]
Click to expand...

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.
 
But set the format in C2 [*]
Click to expand...

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