Libre Office Calc

I'd like to enter the time 5:39 in minutes and seconds in a cell. I highlight the cell and select Format > Cells from the menu. In the 'Format Cells' dialog, I enter MM:SS in the 'Format code' box at the bottom and select 'OK'.

When I then enter '5:39' in the cell, it's immediately changed to '39:00'. If I instead enter the full HH:MM:SS value '00:05:39' in the cell, it does finally show up as '5:39'.

How do I format the cell so that I can type in exactly what I want it to show, which is the MM:SS format '5:39'?

  • What should happen when the user types in 65:39, or 1:05:39? – Blackwood Jul 26 '19 at 1:18
  • @Blackwood It should stay 65:39. As far as I'm concerned, hours don't exist. I guess it would be nice if 1:05:39 were converted to 65:39, but since that entry violates the format, I'm okay with it misbehaving. – Darien Marks Jul 26 '19 at 2:03
  • As fixer1234 wrote, you can't enter MM:SS without hours. But you can make LO accept minute values > 59 by surrounding the MM format code with square brackets: [MM]:SS should accept MM values > 59. – tohuwawohu Jul 26 '19 at 6:12

Unfortunately, you can't input just mm:ss and have LO understand what it means, regardless of how the cell is formatted.

For LO to interpret input as a time, and interpret it correctly, it needs to understand the units. That requires an entry starting with hours and minutes. Then you can format it however you want. Hours can be zero, but you need the placeholder.

So you can so something like:

  • Include a leading 0: for hours as part of your entry.
  • Use a macro to start the input with 0: or to interpret your entry and convert it.
  • Enter mm:ss values as text, or a value that will be misinterpreted, then use a formula in another cell to convert it. If your entry is in A1, B1 could contain: =A1/60 and format the cell with your time format.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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