Ask Your Question

Revision history [back]

Number format for "quantity of time"?

I need a way to format a cell that has a quantity of time, as in 5 days, 2 hours, 14 minutes, and so far haven't found a way to do this with the built-in cell formatting.

The "time" format HH:MM comes close to the sort of behavior I'd want, but fails on values longer than 24 hours. Since HH was designed to be used as part of a date, it loops. The time I would want to display as 25:14 actually renders as 01:14.

To achieve my goals, I've been using the following BASIC function. This does do what I want, and can be used in cells as =FTIME(<some calc time value>).

 Function ftime(calcTime as Double) as string
    Dim mins as Long
    mins = CLong(calcTime * 60.0 * 24.0)
    Dim Negative as Boolean
    Negative = (mins < 0)
    if Negative then 
        mins = 0 - mins
    end if
    dim hrs as long
    dim days as long
    hrs = mins \ 60
    mins = (mins Mod 60)
    dim txt as string
    if negative then 
        txt = "("
    else 
        txt = ""
    end if 
    txt = txt & hrs & ":"
    if mins < 10 then
        txt = txt & "0"
    end if
    txt = txt & mins
    if negative then
        txt = txt & ")"
    end if
    ftime = txt
 End Function

The problem arises because as far as Calc is concerned, the value is now a string. So, when I want to re-use the value elsewhere it must be converted back. I now have cells with formulae like =FTIME(UNFTIME(A5) + UNFTIME(A6)). Using SUMIFS or VLOOKUP requires forces me to make a separate column with unformatted values.

Is there a number format code I've missed that works well with durations? Is there a way to extend the number formatting system from within the context of a macro? Is there a way to write a plugin in Java that would provide a new kind of number format?

Number format for "quantity of time"?

I need a way to format a cell that has a quantity of time, as in 5 days, 2 hours, 14 minutes, and so far haven't found a way to do this with the built-in cell formatting.

The "time" format HH:MM comes close to the sort of behavior I'd want, but fails on values longer than 24 hours. Since HH was designed to be used as part of a date, it loops. only shows the value modulo 24. The time I would want to display as 25:14 actually renders as 01:14.

To achieve my goals, I've been using the following BASIC function. This does do what I want, and can be used in cells as =FTIME(<some calc time value>).

 Function ftime(calcTime as Double) as string
    Dim mins as Long
    mins = CLong(calcTime * 60.0 * 24.0)
    Dim Negative as Boolean
    Negative = (mins < 0)
    if Negative then 
        mins = 0 - mins
    end if
    dim hrs as long
    dim days as long
    hrs = mins \ 60
    mins = (mins Mod 60)
    dim txt as string
    if negative then 
        txt = "("
    else 
        txt = ""
    end if 
    txt = txt & hrs & ":"
    if mins < 10 then
        txt = txt & "0"
    end if
    txt = txt & mins
    if negative then
        txt = txt & ")"
    end if
    ftime = txt
 End Function

The problem arises because as far as Calc is concerned, the value is now a string. So, when I want to re-use the value elsewhere it must be converted back. I now have cells with formulae like =FTIME(UNFTIME(A5) + UNFTIME(A6)). Using SUMIFS or VLOOKUP requires forces me to make a separate column with unformatted values.

Is there a number format code I've missed that works well with durations? Is there a way to extend the number formatting system from within the context of a macro? Is there a way to write a plugin in Java that would provide a new kind of number format?

Number format for "quantity of time"?

I need a way to format a cell that has a quantity of time, as in 5 days, 2 hours, 14 minutes, and so far haven't found a way to do this with the built-in cell formatting.

The "time" format HH:MM comes close to the sort of behavior I'd want, but fails on values longer than 24 hours. Since HH was designed to be used as part of a date, it only shows the value modulo 24. The time I would want to display as 25:14 actually renders as 01:14.

To achieve my goals, As a temporary workaround, I've been using the following BASIC function. This does do what displays as I want, and can be used in cells as =FTIME(<some calc time value>).

 Function ftime(calcTime as Double) as string
    Dim mins as Long
    mins = CLong(calcTime * 60.0 * 24.0)
    Dim Negative as Boolean
    Negative = (mins < 0)
    if Negative then 
        mins = 0 - mins
    end if
    dim hrs as long
    dim days as long
    hrs = mins \ 60
    mins = (mins Mod 60)
    dim txt as string
    if negative then 
        txt = "("
    else 
        txt = ""
    end if 
    txt = txt & hrs & ":"
    if mins < 10 then
        txt = txt & "0"
    end if
    txt = txt & mins
    if negative then
        txt = txt & ")"
    end if
    ftime = txt
 End Function

The problem arises because as far as Calc is concerned, the value is now a string. So, when I want to re-use the value elsewhere it must be converted back. I now have cells with formulae like =FTIME(UNFTIME(A5) + UNFTIME(A6)). Using SUMIFS or VLOOKUP requires me to make a separate column with unformatted values.

Is there a number format code I've missed that works well with durations? Is there a way to extend the number formatting system from within the context of a macro? Is there a way to write a plugin in Java that would provide a new kind of number format?