Ask Your Question

Time as duration.

asked 2017-12-04 01:06:50 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

How do I enter the duration in a cell.. say A1 if Johny walked for x number of hours minutes seconds. And in A2 if Mary walked for x number of hours minutes seconds. This is not a Date-Time question but a time as duration question. Then at the end of the column I want to get the average time the people walked using something like =Average(A1:A12)

Thanks in advance, Mitch

edit retag flag offensive close merge delete


Wiki questions may look kind and modest.
But they are anonymous and don't allow to award credits.
Very experienced users (high "karma") can edit questions by others anyway if needed. Mostly the need arises for better formatting.
Therefore I don't regard the wiki option for questions useful.

Lupp gravatar imageLupp ( 2017-12-04 02:41:56 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-12-04 02:06:49 +0100

Lupp gravatar image

updated 2017-12-04 02:11:26 +0100

If you want to use a specific single unit for your durations as is common in science (and partly in engineering) this isn't a question at all. You have to enter numbers regarding the unit (which will then be implicit) and to calculate with them. To interpret the results for the real world you need to regard the unit again. This can either be the h or the min or the s or any obscure unit I never heard of. It's the same as with any quantity and even with currency.

The specifics concerning time - and this for TOD as well as for durations - are due to the unfortunate habit of using mixed units, and specifically those conflicting with the otherwise used decimal numeric system. If you want to observe this habit you need to accept the one implicit unit of time for which it is supported: the day. For entering durations you will also need to pretend to not know the fundamental difference between TOD values and durations. This accepted you can enter a duration of 123 h 45 min 32.1 s as 123:45:32.1 (in a decimal-point locale). Smart Calc will even automatically assign the 'Numbers' format code [HH]:MM:SS.00 to the cell - again mixing up TOD with duration. However you may change that to [HH]" h "MM" min "SS.00" s" if you don't want to surrender.

Since the actual content of such cells is always an ordinary number based on the unit d (day) the calculations are as with any numbers. Just calculate your =AVERAGE(A1:A12) and format the result in the appropriate way.

If you are a purist and insist on entering durations in a multi-unit-duration-format, 1h12min3.2s e.g. it gets complicated. I cannot think of a way to achieve this without resortiung to user code.

edit flag offensive delete link more


Thanks for this answer , really needed to how too do this

stargazerpjs gravatar imagestargazerpjs ( 2019-03-02 01:12:10 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-04 01:06:50 +0100

Seen: 1,220 times

Last updated: Dec 04 '17