Ask Your Question

MontyJ's profile - activity

2016-09-30 21:31:41 +0200 received badge  Famous Question (source)
2016-03-09 17:53:31 +0200 received badge  Notable Question (source)
2016-02-11 13:38:02 +0200 received badge  Popular Question (source)
2016-02-09 17:27:39 +0200 asked a question 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?