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

W dniu 2013-08-09 02:00, Brian Barker pisze:
> 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

Thanks for comments Brian.
Regards,
gordom


--
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