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,13Each 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:13My 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: [email protected] 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
