Calc - Time and date conversion problems

Hola to All!

I am using LO Version: on Mint Linux. I have struggled for two days with this and cannot find a solution through tons of trial-and-error, or in any online help.

Here are two Calc field contents I am importing from two different external resources that I cannot change the format of:

“ - - [26/Jan/2016:14:33:07]” and “t=2016-02-06T09:30:08”

The goal is to be able to do a time/date difference comparison between these two cells, as well as being able to sort them.

I am doing a ‘mid’ string function on the first to grab the “26/Jan/2016:14:33:07” date and time string, and in the second one doing a ‘mid’ and ‘right’ function to get two strings w/o the “T”, and then using concatenation to stitch them together.

But no matter what cell date/time formats I use, what format I set as system “default” in the options, I cannot come up with a solution to come up with a common date and time in one cell that the system will recognize as valid. I prefer not to have two columns, one for date and one for time as that would probably add a huge amount of complexity to sorting and doing calculations on.

Can it be done in one system recognizable format (sorts properly) like: 02/06/2016 15:10:43?

I am not sure that will sort correctly, but some of the cell formats, using datevalue() show that format.

But I cannot even come up with a conversion process that will directly give me kind of date/time format.

Am I barking up the wrong tree with this approach? Any ideas?



What about Data - Text to Columns ?

@JohnSUN Yes of course, the simpler the better :slight_smile:

But I thought formulas were expected because of “…conversion process that will directly give me…”


Just try it - you’ll like it :slight_smile:
Date-Time From Text.gif

I just wanna say that this solution saved my day!


  1. Calc can convert to date/time a text like 26/1/2016 14:33:07. With text in F1:


So, for the 1st field we have to convert Jan (Feb, Mar, etc) to the month number and to replace the : after the year by a space.

  1. Calc can directly convert to date/time a text like 2016-02-06T09:30:08. With text in F1:


So, for the 2nd field simply remove the first 2 characters.

See DateTimeConversion.ods