How do I format time to MM:SS in Libre Office Calc?

2

Libre Office Calc 6.0.7.3.

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

Darien Marks

Posted 2019-07-26T00:34:42.307

Reputation: 21

What should happen when the user types in 65:39, or 1:05:39? – Blackwood – 2019-07-26T01:18:56.533

@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 – 2019-07-26T02:03:17.467

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 – 2019-07-26T06:12:06.493

Answers

1

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.

fixer1234

Posted 2019-07-26T00:34:42.307

Reputation: 24 254

Asked: 2019-07-26T00:34:42.307

Viewed: 104 times

Active: 2019-07-26T06:42:13.937