Ask Your Question

I am new to this so be nice. Can I show time as, 3hours, 09minutes and 30seconds

asked 2020-09-17 20:55:30 +0200

rbgbf gravatar image

I am trying to show cycle times for production and can not seem to get the correct output.

Say I have a part that takes 11139 second to make and I want this to show as Hours; Minutes; Seconds in a separate field. How would I achieve this?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2020-09-17 22:48:23 +0200

igorlius gravatar image

updated 2020-09-18 04:06:52 +0200

If you need it frequently, you can also use a macro function which you can call from any cell like a normal buildin function. Just put into Tools -> Macros ... -> Edit Macros

  • 1st argument is the total number of seconds as a long (max value is 2147483647, so 24855 days 03 hours 14 minutes 07 seconds )
  • 2nd argument is optinal (default: false) if set to true/1, it shows the leading zero parts. ( 00 days 00 hours 00 minutes, obviously the seconds parts will remain)

Example Output: image description

And here the macro function

function sec2wall(ssecs as string, optional zeros as boolean) as string
    rem desc: converts seconds into walltime "ww week(s) dd day(s) hh hour(s) mm minute(s) ss"
    rem param ssecs (string): number of seconds <= MAX_LONG
    rem param zeros (boolean) : toggle leading zeros (default: false/off)
    rem return out (string)   format string "ww week(s) dd day(s) hh hour(s) mm minute(s) ss"  or "error message"
    on error goto errorhandler rem register error handler
    ssecs = Trim(ssecs) rem remove leading and trailing whitespaces
    invalid = true  rem assume input is bad
    for i = 1 to len(ssecs)  rem check if input is bad
        c = mid(ssecs,i,1)
        select case c
            case "0","1","2","3","4","5","6","7","8","9"
            invalid = false
            case else
                invalid = true
                exit for
        end select
    if(invalid) then
        goto errorhandler
    secs = clng(ssecs)
    on error goto 0 rem reset error handler
    quants = array( 7*24*60*60, 24*60*60, 60*60, 60)
    labels = array( "week", "day", "hour", "minute")
    zeros = iif(ismissing(zeros), false, zeros)
    out = ""
    for i = lbound(quants) to ubound(quants)    
        c = iif (secs > quants(i), int(secs/quants(i)), 0)
        secs = secs - (c*quants(i))
        if( out <> "" or  c <> 0  or zeros ) then
            out = out & iif(c < 10, "0", "") &  c & " " & labels(i) & iif(c > 1 or c = 0, "s","") & " "
    sec2wall=out & iif(secs < 10, "0", "") &  secs & " " & "second" & iif(secs > 1 or secs = 0, "s","") & " "
    exit function
        sec2wall="err: invalid argument not a positiv number or larger than 2147483647"
    exit function
end function

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

Have a nice day and let's (continue to) "Be excellent to each other!"

Ask / Getting Started:

edit flag offensive delete link more


Much appreciated. I will need to grow into this type of work but will definitely look this over. Thank you for you sharing the information regarding the site etiquette. Sorry for my ignorance, I'm learning. Please correct me when need be.

rbgbf gravatar imagerbgbf ( 2020-09-17 22:57:42 +0200 )edit

No worries. Hope you'll have a great time here.

igorlius gravatar imageigorlius ( 2020-09-17 23:28:04 +0200 )edit

Using d (day) and week as if they are units of time suffers from the fact that they aren't. Day and week haven't an exact duration themselves due to being used for calendaric purposes, and therefore being subject to arbitary administrative regulations. As long as the silly "DaylightSavingTime" exists, there are days of 23 h or 25 h twice a year. If production times are calculated with a resolution to seconds, this may be relevant. At least if such a duration is added to a calendaric TimeOfStart to get the TimeOfCompletion, we need to regard this effect.
[In addition there is the mostly negligibly small effect of UTC inserting leap seconds now and then, trying to hide the effects of decreasing speed of earth rotation, and to keep (e.g.) specific periodic astronomical events synchronized with general TOD. See]

Lupp gravatar imageLupp ( 2020-09-18 12:47:00 +0200 )edit

@Lupp It's just a human readable format (string) for elapsed time. For humans it nicer/easier to read than the seconds as a number. But i agree with you that i would not use it for further automated processing (aka. calculation) with real "time units".

igorlius gravatar imageigorlius ( 2020-09-18 13:47:52 +0200 )edit

I thought so. Just took another opportunity to point to some facts often disregarded.
Spreadsheets even haven't a way to handle the DST hokum, but are nonetheless used sometimes to calculate durations where an hour may make a relevant difference.
Quoting Lupp: "The problem isn't the problem. It's the people."

Lupp gravatar imageLupp ( 2020-09-18 14:24:09 +0200 )edit

@Lupp: That last quote ... sounds a bit familar.

image description

I'll show myself out now.

igorlius gravatar imageigorlius ( 2020-09-18 14:38:22 +0200 )edit

answered 2020-09-17 21:17:03 +0200

Lupp gravatar image

updated 2020-09-17 21:42:59 +0200

If you want to use the special support for formatting of time values, you must use the day (d) as the implicit unit of time in spreadsheets. If you have given a time by counting seconds, you need therefore to multiply divide the number by 86400 (number of seconds in an ordinary day). The resulting time value you can then format for display using the format code [H]" h "MM" min "SS" s" where the square brackets enclosing the format code H are needed to allow for values >= 24 h.
If you want the shown time to be represented as text, you can use the formula
=Text(A2/86400;"[H]"" h ""MM"" min ""SS"" s""") where A2 is an example of a reference to the respective time counted by seconds (cell A2 here).

Please note that the colon notation is (by standards) made for TimeOfDay, while durations should be expressed with units like h, min, s. Unfortunately this aggravates the usage of durations given as text. Since you anyway have your durations calculated somewhere else in seconds, this shouldn't be a problem.
Also note that in spreadsheets shown units are only made for the display by formatting which may lie. Spreadsheets never calculate with units.

edit flag offensive delete link more


Thanks, that works as well as the answer from the previous sender.

rbgbf gravatar imagerbgbf ( 2020-09-17 22:20:26 +0200 )edit

answered 2020-09-17 21:11:59 +0200

erAck gravatar image

All time values are internally expressed as fractions of days; assuming the value 11139 in A1 use formula =A1/86400 and format as [HH]:MM:SS in which the [HH] part handles and displays a duration of more than 24 hours.

edit flag offensive delete link more


Cool. That did it. I'm learning.

Thank you.

rbgbf gravatar imagerbgbf ( 2020-09-17 22:20:54 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-17 20:55:30 +0200

Seen: 71 times

Last updated: Sep 18 '20