Hi,
All the values in column B in the testdata1 sheet are of course Text values. They need splitting into date & time numbers.
One way would be to use the Data TextToColumns functionality using the fixed length option, then convert what are still text values to numbers by entering a zero in a cell, copying it, then select the two columns and use Paste Special ADD.
ALternatively I'd just string slice the text
To get the date number use
Formula:
=DATEVALUE(LEFT(SUBSTITUTE($B3,".","/"),10))
and the Time number
Formula:
=TIMEVALUE(RIGHT($B3,8))
The other test 2 sheet does already contain date & time numbers. YOU can always use =ISNUMBER(A1) or ISTEXT(A1) to determine whether a cell is a number or text.
Bookmarks