Using time & data functions

J

jonasa

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
 
J

jonasa

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

Ian

Try 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))
 
S

SteveG

If you need to get the fractional seconds try this.

=MID(REPLACE(A1,12,11,MID(A1,12,8)&"."&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)&"."&RIGHT(A1,2)),7,4),MID(REPLACE(A1,12,11,MID(A1,12,8)&"."&RIGHT(A1,2)),4,2),LEFT(REPLACE(A1,12,11,MID(A1,12,8)&"."&RIGHT(A1,2)),2)),"mm/dd/yyyy")&"
"&MID(REPLACE(A1,12,11,MID(A1,12,8)&"."&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
 
J

jonasa

Great help from you guys! Thanks.
Steves suggestion answered my next question - perfect!

One thing I don't understand is that in "my" Excel2003 I need to write
=MID(REPLACE(B10;12;11;MID(B10;12;8)&"."&RIGHT(B10;2) );12;11)
instead of Steves
=MID(REPLACE(A1,12,11,MID(A1,12,8)&"."&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 "," but I need to change them to
";", 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
 
V

vezerid

Jonas

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 "," for
decimal separator and thus semicolon (the list separator) is used for
function arguments. Anglosaxon settings typically use "." for decimal
separator, thus "," 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(" ",A1)-1) ---- Formatting as
dd.mm.yy

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

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

=DATEVALUE(LEFT(A1,FIND(" ",A1)-1)+TIMEVALUE(SUBSTITUTE(MID(A1,FIND("
",A1),LEN(A1)),",","."))

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

HTH
Kostis Vezerides
 
Top