Re: [libreoffice-users] [Calc] how to make this formula more "compact"?

At 00:03 09/08/2013 +0200, Gordom Noname wrote:
> I have a series of data. Here you have a small sample (this data is 
> placed into a column A):
> ,,08,01,17,27,13
>
> Each cell must be formatted into a proper date & time format. I'm 
> achieving this using the following formula in column B (the 
> following example corresponds to cell No A1):
> =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A1;1;1;"2013");11;1;" 
> ");5;1;"-");8;1;"-");14;1;":");17;1;":")
>
> As the result I get this in cell B1:
> 2013-08-01 17:27:13
>
> My question: Is there a way to simplify the formula? However it 
> works, it doesn't seem to be very elegant right now.

I'd probably do something like
="2013-"&MID(A1;3;2)&"-"&MID(A1;6;2)&" 
"&MID(A1;9;2)&":"&MID(A1;12;2)&":"&RIGHT(A1;2)
instead.  Only you can decide whether this is more elegant.

But notice that your formula does not create what I would call a 
"proper date and time format", but instead a text string that looks 
like a date and time value.  (So does my version, of course.)  To see 
the difference, try adding 1 to the date and time value.  In your 
case you will get the result 1, since the text string is treated as 
zero, but adding one to a genuine date and time value produces a time 
exactly one day later.  You can convert the result of either formula 
to a real date and time using
=DATEVALUE(B1)+TIMEVALUE(B1)
and then formatting the result cell appropriately (as YYYY-MM-DD HH:MM:SS).

You can build this idea into my version by using
=DATEVALUE("2013-"&MID(A1;3;2)&"-"&MID(A1;6;2))+TIMEVALUE(MID(A1;9;2)&":"&MID(A1;12;2)&":"&RIGHT(A1;2))

But that leads to what is perhaps a simpler version:
=DATE(2013;MID(A1;3;2);MID(A1;6;2))+TIME(MID(A1;9;2);MID(A1;12;2);RIGHT(A1;2))
Again, you need to format the result cell to display the date and 
time as you wish to see them.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted