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