Ask Your Question
0

Calc - Time and date conversion problems

asked 2016-02-08 22:57:44 +0100

MontyJ gravatar image

updated 2016-03-09 17:54:14 +0100

Alex Kemp gravatar image

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 flag offensive close merge delete

Comments

1
JohnSUN gravatar imageJohnSUN ( 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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-02-11 13:42:40 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2016-02-11 12:13:50 +0100

JohnSUN gravatar image

Just try it - you'll like it :-) Date-Time From Text.gif

edit flag offensive delete link more
0

answered 2016-02-11 11:18:00 +0100

pierre-yves samyn gravatar image

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.

See DateTimeConversion.ods

Regards

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-02-08 22:54:07 +0100

Seen: 368 times

Last updated: Feb 11 '16