Calc: user defined number format for time duration showing hours and minutes

[seanmadsen]

I have some cells in a LibreOffice Calc document the represent time duration (not time of day). I can set the format code to H:MM and it looks pretty good. For example four and a half hours can be entered by typing 4:30 or by typing 0.1875 (because it’s 0.1875 days) and the cell will show 4:30. This is good.

But when I have a value over 24 hours, then the format does not produce a sensible display. For example, if I type 42:00 or type 1.75 I would like to see 42:00 but instead I see 18:00. This result makes sense to me, given the format code I’m using – it’s just showing the time of day on the next day.

I would like to find a number format code that will format 0.1875 as 4:30 and also format 1.75 as 42:00. Is this possible?

[seanmadsen]

This format code [>=1]0.## "days";H:MM "hrs" doesn’t quite satisfy the requirement, but it might be a suitable work-around if no other answers appear. It gives:

• 0.1875 → 4:30 hrs
• 1.75 → 1.75 days

[mariosv]

“I would like to find a number format code that will format 0.1875 as 4:30 and also format 1.75 as 42:00. Is this possible?”

Yes it is: [H]:MM square brackets forces to show the total.

[seanmadsen]

Yes! That works. Thank you!