How to prevent Time function in LibreOffice Calc from wrapping around every 24 hrs?

Question

I'm using LibreOffice Calc for some calculations. Why this function Time(100,0,0) returns 4:00 even when the cell is formatted as [HH]:MM?

Answer 1

You cannot do this with the built-in TIME() function - the maximum value this can create is 23:59:59. Take it for granted, as an inevitability, as a natural disaster. After all, your alarm clock doesn't have a 100 hour 00 minute division either.

To work with time ranges longer than a day, you have to write your own custom function. Something similar to this:

Function DateTime(Optional HH As Long, Optional MM As Long, Optional SS As Long) As Double 
Rem Return [date-]time value as Double
Rem Params:
Rem No params - current date and time
Rem 1 - hours
Rem 2 - hours-minutes
Rem 3 - days-hours-minutes
    If IsMissing(HH) Then 
        DateTime = Timer()/86400
    Else
        If IsMissing(SS) Then SS = 0
        If IsMissing(MM) Then MM = 0
        DateTime = (HH*3600+MM*60+SS)/86400
    EndIf 
End Function