Search code examples
libreoffice-calc

LibreOffice Calc convert hours and minutes into decimal fraction without roll-over

I have a summed column of time values formatted as HH:MM. The summation is formatted as [HH]:MM which prevents the roll-over when HH > 23. I would like this value to be converted into a decimal number representation. For example:

       A        B
 1   12:25
 2   11:25
 3   03:25
 4   
 5   27:15    27.25

A5 is formatted as [HH]:MM and B5 contains '=A5' formatted as 0.00. However, this produces a mod 24 result of 3.25 instead of 27.25. I cannot seem to find any way of formatting the cell B5 to show the actual decimal hours equivalent of A5.

Solution

  • After thinking on this further I realised that the value of the summation has to be a decimal number to begin with. And evidence suggested that the value represents days and fractions thereof. So, the answer to my question is simply take A5 and multiply that by 24 using 0.00 as the format.