How to do this in Calc

users
#1

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.

Thanks

#2

Thanks for reading, but I just manually formatted one page, and then I can
copy/paste to other pages.

Sorry for any inconvenience.

I'd still like to know how to do it though. I'm thinking a macro is really
what I need.

#3

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

#4

If this is a simple string, then the extract is quite simple. But if it contains date formulas in a way, it is a wee more complicated. But let us start with the simple assumption, that the string is exactly the one you give, including e.g. the lacking space before "to":

Assuming your string is in cell A1, cell B1 could contain:

=CONCATENATE(MID(A1,12,5)," - ",MID(A1,34,5))

A wee more safe could be:

=CONCATENATE(MID(A1,12,5)," - ",LEFT(RIGHT(A1,8),5))

Both these give the result you wanted. But what if the date is not the same, which result do you wish then? In that case I think you should do transforms into date format (DATEVALUE), but much depends on what you really want.

#5

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

Comments are inline below ...

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

#6

Sorry for my posts going all over the place. Just learning how to use gmail
for a mailing list.

See below...

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)

​When I tried that I get in column B:

e lnf -

​

#7

See below...

If this is a simple string, then the extract is quite simple. But if it
contains date formulas in a way, it is a wee more complicated. But let us
start with the simple assumption, that the string is exactly the one you
give, including e.g. the lacking space before "to":

Assuming your string is in cell A1, cell B1 could contain:

=CONCATENATE(MID(A1,12,5)," - ",MID(A1,34,5))

​When I tried that I get in column B:

e lnf -
​

A wee more safe could be:

=CONCATENATE(MID(A1,12,5)," - ",LEFT(RIGHT(A1,8),5))

​When I tried that I got in column B:

e lnf - ormat​

#8

Which language are you using in Calc? The commands are language dependant. Those I wrote is for an English version.
/Kaj

#9

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

The CSV file will contain just text, of course - but that could be interpreted as numbers, dates, times or whatever when opened in Calc. So that's no guarantee.

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

I'm hoping this isn't all in one cell! And do you now mean that each value is preceded by two spaces? That will throw out the suggested solution, of course.

If you data is regular, it might be easier to construct the values directly.

You lost me here.

This suggestion might have been helpful if you were constructing the data yourself - but probably not if you are importing data as you now clarify.

Try:
=MID(A1;12;5)&" - "&MID(A1;34;5)

When I tried that I get in column B:
e lnf -

In that case, the five characters picked up at position 12 must be ee-space-ell-en-eff and there must be nothing at position 34: that's probably off the end of whatever is there. Are you sure that A1 contains your first data and not some sort of heading? Have you transcribed that wrongly and the lower case ell is actually a capital I? Does A1 says something like "Hourly Usage Information"? That would produce the effect you describe. You won't be able to harvest any times out of that heading! You will need to make intelligent adjustments to the formula to fit your actual row numbers.

I trust this helps.

Brian Barker

#10

​Sorry for the late reply, life got in the way.​

Try:

=MID(A1;12;5)&" - "&MID(A1;34;5)

​This works, after I realized that I had to ​change A1 to the appropriate
reference number.

Thanks! To all that replied, it all helped.

Cheers,

#11

​In all fairness to Kaj, his formulas also worked!

​Thanks Kaj!

​