CODEWITHSUNDEEP
libreoffice-calc
Charles Goodwin
Charles Goodwin

Reputation: 6642

Libreoffice Calc: date string to date/time values

I need to extract a 'date string' as separate columns representing date/time data.

 | A                        | B                 | C
-+--------------------------+-------------------+-----------------
1| Datetime String          | Date Value        | Time Value
2| Sat 09 Sep 2017 20:00 PM | =DATEVALUE(A2)    | =TIMEVALUE(A2)

The above functions DATEVALUE and TIMEVALUE return errors. I'm at a bit of a loss at the moment.

Upvotes: 10

Views: 13181

Answers (3)

Tim Abell
Tim Abell

Reputation: 11881

Just in case anyone else comes across Cater Allen's new export format: 25Dec2020 (i.e. DDMMMYYYY) that isn't recognised during CSV import:

=DATEVALUE(NUMBERVALUE(MID(A2,6,4))&"-"&MID(A2,3,3)&"-"&NUMBERVALUE(MID(A2,1,2))) will turn it into a proper date that YNAB can recognise.

Thanks for the question and other answers that helped me get there neatly.

Upvotes: 0

Jim K
Jim K

Reputation: 13810

It looks like the date string is fixed width, so use this formula for cell B2, which builds a date string in YYYY-MMM-DD format and then converts with DATEVALUE.

=DATEVALUE(MID(A2,12,4) & "-" & MID(A2,8,3) & "-" & MID(A2,5,2))

The formula for cell C2 only needs to extract the time, which is already in a suitable format.

=TIMEVALUE(MID(A2,17,5))

Then go to Format -> Cells, and format B2 as a date and C2 as a time.

Upvotes: 12

John B. Walugembe
John B. Walugembe

Reputation: 589

You could try =MID(A2,1,FIND(":",A2)-4) for the Date Value, and =MID(A2,FIND(":",A2)-2,8) for the Time Value.

Upvotes: 2

Related Questions