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

I'll first reply to Brian's post and hopefully that will answer everybody's
questions.

Comments are inline below ...

On Sat, Feb 7, 2015 at 4:44 PM, Brian Barker [via Document Foundation Mail
Archive] <ml-node+s969070n4139312h48@n3.nabble.com> wrote:

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

​Yes, I believe the data is text, since it is in a .csv file.

Column A is precisely like this:

  2015-01-22 00:00:00to 2015-01-22 01:00:00  2015-01-22 01:00:00to
2015-01-22 02:00:00  2015-01-22 02:00:00to 2015-01-22 03:00:00  2015-01-22
03:00:00to 2015-01-22 04:00:00
​..
..
..
  2015-01-22 23:00:00to 2015-01-23 00:00:00
​And yes, there is no space before the "to" word.

It's a daily tabulation of my electric use from my utility provider on an
hour by hour basis.

I want to convert the long date-time column to a shorter version to make it
more useful in charts.

Like I said, I did do one page manually, but it's still tedious. ​





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

​I'll try it and see how it works.​



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

​You lost me here. [?]​



> I trust this helps.
>
> Brian Barker


336.gif (274 bytes) <http://nabble.documentfoundation.org/attachment/4139314/0/336.gif>




--
View this message in context: 
http://nabble.documentfoundation.org/How-to-do-this-in-Calc-tp4139304p4139314.html
Sent from the Users mailing list archive at Nabble.com.
-- 
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