# Calc - Time and date conversion problems

Hola to All!

I am using LO Version: 5.0.3.2 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:

"127.0.0.1 - - [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?

Thanks!

Monty

edit retag close merge delete

1
( 2016-02-11 11:23:49 +0100 )edit
1

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

But I thought formulas were expected because of "...conversion process that will directly give me..."

Regards

( 2016-02-11 13:42:40 +0100 )edit

Sort by » oldest newest most voted

Just try it - you'll like it :-)

more

Hi

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

=DATEVALUE(F1)+TIMEVALUE(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.

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

=DATEVALUE(F1)+TIMEVALUE(F1)


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

Regards

more