How to Split CSV timestamp using formula ?

General discussion on OpenOffice Calc and its spreadsheets.

How to Split CSV timestamp using formula ?

Postby UniSET » Mon Sep 15, 2014 6:57 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
Posts: 22
Joined: Tue Mar 25, 2014 6:33 am

Re: How to Split CSV timestamp using formula ?

Postby SteveJ » Mon Sep 15, 2014 11:27 am


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

Posts: 20
Joined: Tue Mar 25, 2014 6:31 am

Return to Calc

Who is online

Users browsing this forum: No registered users and 0 guests