Find Average hh:mm from Decimal

[prees959]

HI

I have a decimal hh:mm field and would like to calculate the actual average hours and minutes from this field.

Thank you.

Phil

[jonathandienst · Accepted solution]

By decimal, I assume you mean decimal hours (ie 2.5 indicating 2h and 30 minutes).

To display as hours and minutes, divide the value by 24 and format as an interval or as a time

=Interval(myField / 24, 'hh:mm')

=Time(myField / 24, 'hh:mm')

[jonathandienst]

Making the same assumption, to get the hour value:

=Floor(myField)

The minute value

=Round(Frac(myField) * 60)

[prees959]

great ! worked perfectly.

Additionally I need to find the average hh:mm based on this decimal field.  Is this possible?

Thanks,

Phil

[Or]

hh:mm isn't a decimal. If that's your format, it's probably a string.

If you meant Hours.Minutes, e.g. 7.30 is 7 hours and 30 minutes - you can convert this to be measured in hours:

floor(Field) + (frac(Field)*5/3)

Which will result in a value of 7.50 (7 and a half hours).

You can then average this field, and if you want to display it in the original format, you can convert it back using

Floor(Field) + (frac(Field)*3/5)

Which in our case would convert back to 7.30.

If your value is a string, convert it to a number first and then work based on that.

[prees959]

Hi,

I used your formula in this way  :

Interval(avg([Updated Time] / 24), 'hh:mm')

Thanks for your help

Phil

[jonathandienst]

>>I need to find the average hh:mm based on this decimal field.

In the load script or front end? Average of the hours only or hours and minutes? Simplest scenario

=Interval(Avg(myField) / 24, 'hh:mm')

=Time(Avg(myField) / 24, 'hh:mm')