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] <[email protected]> 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: [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
