Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I have some cells which contain times entered in by users, which are written in the following format: mm:ss. There are never any hours. The seconds always have leading zeroes. The minutes may or may not have leading zeros.

Because I need to separate out the minutes and seconds for various operations, I need to be able to get at the numbers before and after the colon.

How do I select the number before a colon in a cell, and how do I select the number after?

share|improve this question

1 Answer

up vote 1 down vote accepted

You should just use the internal time functions MINUTE() and SECOND(). They're designed to get the minute/second component from a time value.

Edit:
Actually, if you've got a problem with users entering the incorrect time format, the solution is not quite as simple as I wrote in the comment, but it's still fairly close:

 =CONCATENATE("0:",HOUR(F421),":",MINUTE(A1))

This basically shifts each time component one unit to the right. The hours become minutes, the minutes become seconds.

You could use the FIND(), LEN(), LEFT(), RIGHT() functions to do string manipulations and re-implement the time functions you need, but you'd essentially be fighting the software rather than working with it. And in the long run, you always get more done working with the software rather than against it.

If you still want to do it that way, it'd basically be something like:

Minute:

=LEFT(A1, FIND(":", A1), -1)

Second:

=RIGHT(A1, LEN(A1) - FIND(":", A1))
share|improve this answer
Unless I'm missing something, that doesn't help me extract the minutes and seconds from the format mm:ss. I can not change the input format, so if there's a way those functions can take mm:ss and return the minutes, please be more specific. – Dave M G Apr 10 '12 at 4:45
@Dave: It doesn't matter what format the time is in as long as it's a LibreOffice recognized time format. If that cell is set to display numbers in a time format, and it has a value that Calc is able to display, then MINUTE() and SECOND() will extract the minute/second components. – Lèse majesté Apr 10 '12 at 4:48
Okay, I'll try it that way, but now I'm having trouble with the time format and input. I set the format of the cell to MM:SS, and then tested it by entering 4:00, and it displayed 00:00:00. – Dave M G Apr 10 '12 at 4:51
@Dave: That's because 4:00 to Calc is 4:00:00. If you want Calc to actually recognize it as 00:04:00, then you need to type that. Otherwise, none of your cells will have the correct values, and you won't be able to use any of Calc's time functions. In which case, you may as well set the display mode to "text", and then you'll have to write your own time parsing/calculation functions. But that's essentially re-inventing the wheel. – Lèse majesté Apr 10 '12 at 4:52
I think I'll have to do that, because in my situation I can't impose upon the users entering the times to do it in 00:00:00 format. It's too bad Calc is too restrictive about how time has to be entered, but I'd rather modify calc than modify people. Still, +1 for trying to help. :) – Dave M G Apr 10 '12 at 4:56
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.