It's not necessary to split the fields to access them.

If the date_time value is in A2,

B2: =INT(A2) will give you the date (the integer part)

C2: =MOD(A2;1) will give the time (the decimal part)

You'll likely want to format the results as date and time to make the numeric values readable.

If you want to split the values manually, or by a formula, then you should configure the text/csv import to not convert date/time fields but instead leave them as text: uncheck "Detect special numbers" option.

Once you have the date_time as text, then your formulas should work to split the values--again, as text.

If you want actual date & time values, you can use

DATEVALUE(LEFT(A2;FIND(" ";A2)-1))

TIMEVALUE(MID(A2;FIND(" ";A2)+2;255))

Or, just convert the whole thing: B2: =VALUE(A2) and use the INT/MOD formulas to get the parts. This seems like a long way around.

Right. Because you're feeding text to text functions which produce text.

If Calc recognizes and converts the date_time fields to its numeric value, the text formulas won't work as expected because you're feeding them a numeric value.

Thanks,

Statistics: Posted by SteveJ — Mon Sep 15, 2014 11:27 am

]]>

I'm exporting a long list of timestamps from the online form software Wufoo as a CSV. The date and time information appears in a single cell in Open Office as follows:

2014-09-04 14:25:28

I would like to split this content into two cells, date and time, so that I can reference the content individually. I've tried the following:

B2=LEFT(A2;FIND(" ";A2)-1)

C2=MID(A2;FIND(" ";A2)+2;255)

without success.

Strangely, if I change A2 to:

'2014-09-04 14:25:28

then the above formula works to split the cells, but provides text as opposed to usable data.

It is also important that this is done as a formula rather than a command, as this will be repeated many times and should be automated.

Thanks in Advance

Statistics: Posted by UniSET — Mon Sep 15, 2014 6:57 am

]]>