Re: [libreoffice-users] How to do this in Calc

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