Ask Your Question

Will AVERAGE work on Time formated num?

asked 2018-04-25 15:42:43 +0200

abi gravatar image

2:35 or must it be 2.5?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-04-25 16:07:07 +0200

Lupp gravatar image

Concerning the example:
If 2:35 is displayed for a standard time-value, the numerical representation should be 0.107638889
(155/24/60 = 155/1440 = 31/288).

Please note that also a date-time value containing a (high) integer part can be displayed this way if formatted H:MM. Time-formatted display is often lying. 2.5 on the other hand, interpreted as a time, would be 2 and 1/2 days being 60 h (60:00).

Concerning AVERAGE() it will work on numbers independent of how they are formatted. You may need to format the result properly if you want to get it in a specific way.

Once again: Formatting of numbers sometimes suppresses a more significant part. Arithmetic operators and functions will, however, always include the complete value.

edit flag offensive delete link more


Just adding that if duration times > 24h are to be handled or expected of a calculation, the format code [H]:MM will display the proper duration hours instead of wrapping them to "wall clock" values.

erAck gravatar imageerAck ( 2018-04-26 13:11:14 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-25 15:42:43 +0200

Seen: 499 times

Last updated: Apr 25 '18