How to *format* time duration as hh:mm:ss in LibreOffice/Excel?

Question

I would like to copy the time duration (in seconds) to a cell, then have it displayed as (hh:)mm:ss or similar. For example:

Input: 0.49
Display: 0.49 or 0.49s or 490ms (any will do)

Input: 857.26
Display: 14:17 or 14m17s

I know there are ways of doing this 1) when the time duration is entered in hh:mm:ss format, or 2) using a formula/macro to have the content of a cell converted into another cell, where it is shown as desired. I was wondering if it is possible using the format dialog (similarly to custom date/time formats, which I know are possible), so that after filling in a cell the formatting is done "in place".

Answer 1

Hopefully, this will give you some ideas and point you in the right direction.

I see I should have spell checked that, but you'll understand.

Answer 2

To convert the value "in-place", select the cells you need to convert and run this macro:

Sub ConvertSecondsToTime()
    Dim r As Range, tm As Double
    Dim v As Double
    v = 86400
    
    For Each r In Selection
        tm = r.Value / v
        r.Value = tm
        r.NumberFormat = "[hh]:mm:ss.000"
    Next r
End Sub

Before:

and after: