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?
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.
Thanks, that works as well as the answer from the previous sender.
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.
Cool. That did it. I’m learning.
Thank you.
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:
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
next
if(invalid) then
goto errorhandler
endif
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","") & " "
endif
next
sec2wall=out & iif(secs < 10, "0", "") & secs & " " & "second" & iif(secs > 1 or secs = 0, "s","") & " "
exit function
errorhandler:
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:
https://wiki.documentfoundation.org/Ask/Getting_Started
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.
No worries. Hope you’ll have a great time here.
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 Leap second - Wikipedia]
@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”.
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.”
