software

I have imported data from a measurement instrument to a text file.
After importing the data to excel the date and time info for each
measurement sample is in a cell formated as:

14.03.2006 10:11:17,82

What is your question?

--
Ian
--
quot;jonasaquot; gt; wrote in
message ...
gt;
gt; I have imported data from a measurement instrument to a text file.
gt; After importing the data to excel the date and time info for each
gt; measurement sample is in a cell formated as:
gt;
gt; 14.03.2006 10:11:17,82

How can I extract/convert this text into a excel time value?

Maybe the answer is so obvious it is hard to see it is a question:
Anyway:
If I use

=TIMEVALUE(A1)
I get a #Value!
error. I get the same error almost regardless of which date or time
function I use.--
jonasa
------------------------------------------------------------------------
jonasa's Profile: www.excelforum.com/member.php...oamp;userid=31722
View this thread: www.excelforum.com/showthread...hreadid=522149Try these
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))
=TIME(MID(A1,12,2),MID(A1,15,2), MID(A1,18,2))--
Ian
--
quot;jonasaquot; gt; wrote in
message ...
gt;
gt; How can I extract/convert this text into a excel time value?
gt;
gt; Maybe the answer is so obvious it is hard to see it is a question:
gt; Anyway:
gt; If I use
gt;
gt; =TIMEVALUE(A1)
gt; I get a #Value!
gt; error. I get the same error almost regardless of which date or time
gt; function I use.
gt;
gt;
gt; --
gt; jonasa
gt; ------------------------------------------------------------------------
gt; jonasa's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31722
gt; View this thread: www.excelforum.com/showthread...hreadid=522149
gt;

If you need to get the fractional seconds try this.

=MID(REPLACE(A1,12,11,MID(A1,12,8)amp;quot;.quot;amp;RIGHT(A1,2) ),12,11)

Custom format this cell as: hh:mm:ss.00.

If you want to see both the date and the time in the cell, this worked
for me. There may be an easier way.

=TEXT(DATE(MID(REPLACE(A1,12,11,MID(A1,12,8)amp;quot;.quot;amp;R IGHT(A1,2)),7,4),MID(REPLACE(A1,12,11,MID(A1,12,8) amp;quot;.quot;amp;RIGHT(A1,2)),4,2),LEFT(REPLACE(A1,12,11,MID(A 1,12,8)amp;quot;.quot;amp;RIGHT(A1,2)),2)),quot;mm/dd/yyyyquot;)amp;quot;
quot;amp;MID(REPLACE(A1,12,11,MID(A1,12,8)amp;quot;.quot;amp;RIGHT(A1,2 )),12,11)

Custom format the cell as: mm/dd/yyyy hh:mm:ss.00

Either one of these enabled me to perform calculations on the time
only.

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=522149
Great help from you guys! Thanks.
Steves suggestion answered my next question - perfect!

One thing I don't understand is that in quot;myquot; Excel2003 I need to write
=MID(REPLACE(B10;12;11;MID(B10;12;8)amp;quot;.quot;amp;RIGHT(B10 ;2) );12;11)
instead of Steves
=MID(REPLACE(A1,12,11,MID(A1,12,8)amp;quot;.quot;amp;RIGHT(A1,2) ),12,11)

It is of course the same with all functions when I get them from the
menus or help system. They all use quot;,quot; but I need to change them to
quot;;quot;, this took me some time to figure out the first time.

Why is this so? Is it a language version/regional version thing or
what?

/Jonas--
jonasa
------------------------------------------------------------------------
jonasa's Profile: www.excelforum.com/member.php...oamp;userid=31722
View this thread: www.excelforum.com/showthread...hreadid=522149Jonas

The choice of ; or , has to do with your computer settings (Start |
Control Panel | International Settings - or Regional Settings,
depending on version). Most European country settings use the quot;,quot; for
decimal separator and thus semicolon (the list separator) is used for
function arguments. Anglosaxon settings typically use quot;.quot; for decimal
separator, thus quot;,quot; is used for list separator.

After reading the previous posts, I think I can suggest some variants,
which take into account variable lengths:

For extracting the date:
=DATEVALUE(LEFT(A1,FIND(quot; quot;,A1)-1) ---- Formatting as
dd.mm.yy

For extracting the time:
=TIMEVALUE(SUBSTITUTE(MID(A1,FIND(quot; quot;,A1),LEN(A1)),quot;,quot;,quot;.quot;))
---- Formatting as hh:mm:ss.00

Adding the two, you convert the entire input in a time value:

=DATEVALUE(LEFT(A1,FIND(quot; quot;,A1)-1) TIMEVALUE(SUBSTITUTE(MID(A1,FIND(quot;
quot;,A1),LEN(A1)),quot;,quot;,quot;.quot;))

A single cell with both should be formatted as
dd.mm.yy hh:mm:ss.00

HTH
Kostis Vezerides