Ask Your Question

Number format for "quantity of time"? [closed]

asked 2017-06-05 22:12:55 +0200

NameTakenPleaseChooseAnother gravatar image

updated 2020-10-06 12:36:00 +0200

Alex Kemp gravatar image

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.

As a temporary workaround, I've been using the following BASIC function. This 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 = "("
        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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-06 12:35:44.493991

1 Answer

Sort by » oldest newest most voted

answered 2017-06-06 05:20:55 +0200

You may use square brackets for most significant part of your time format: [HH]:MM:SS, or [MM]:SS, to show time like "12300:00:21" or "738000:21".

You may use the fact that date/time is actually a float that measures in days, i.e. whole part is number of days, and fractional part is fraction of day (i.e. time), so 0.5 is 12:00 AM (noon). So, a formula like =TRUNC(A1)&" days "&TEXT(MOD(A1,1),"HH \h\r MM \m\i\n SS \s\e\c") might do the job. This creates a string indeed, but in this case, you would probably want to keep time spans in a different (hidden?) column which were used for further calculations, and this formula would serve the purpose of displaying data to user.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-06-05 22:12:55 +0200

Seen: 1,347 times

Last updated: Jun 06 '17