At 15:22 07/02/2015 -0700, Jerry Van Brimmer wrote:
Column A has the following data:
2015-01-06 00:00:00to 2015-01-06 01:00:00
I want a formula in column B to convert it to this:
00:00 - 01:00
This looks easy, I just don't have the know how to do it. It's
basically a copy, minus the yyyy-mm-dd, and replacing the "to" with
a dash. You can just point me in the right direction if you want,
any help appreciated.
With that intervening "to", the data in column A must be text, then?
And the result you want must also be text. Is column A formatted
precisely in that way in all (relevant) rows? If so, as you say, you
just need to copy some parts and reassemble them.
Try:
=MID(A1;12;5)&" - "&MID(A1;34;5)
That's the five characters starting at the twelfth and the five
characters starting at the thirty-fourth, strung together with
space-hyphen-space in between. (Is there actually a space before "to"
in your data? If so, you'll want 35 in place of 34.)
If you data is regular, it might be easier to construct the values
directly. For example, you could enter 00:00 in X1 and 01:00 in X2
and fill down the column. Then in Y1 enter
=TEXT(X1;"HH:MM")&" - "&TEXT(X1+1/24;"HH:MM")
and fill that down the column. The TEXT() function returns the time
in the format you require. Numeric times are stored as fractions of a
day, so adding 1/24 adds an hour to each time.
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
