## Forum Discussion

### 4 Replies

- MarkShnier__YouQrew LegendWell, under the motto of don't let the things that you cannot do stop you from doing the things that you can, I offer the suggestion.

You can use a formula to convert the time of day field to the number of hours past midnight, using a formula numeric field.Then you can use a formula query to get a total of those values, and also use a formula query to get a count of those values, and then divide one by the other to get your average.

Once you have that you can turn it back to a time of day using a formula.

------------------------------

Mark Shnier (YQC)

mark.shnier@gmail.com

------------------------------- JosephGomezQrew MemberThanks, Mark. I managed to implement this using a formula field, then in summary reports via summary formula for avg, then converted back to ToD value. Appreciate it.

------------------------------

Joseph Gomez

------------------------------

- JohnCroslandQrew Trainee
Good morning,

If you want to do this via Formula Queries, then you can convert the time of day into decimal hours. You can then use a Sum Values on that field and then divide by the count to get the average. If you need it to be in a TimeOfDay format, then you can convert it back into TimeOfDay.

If you need to use a Formula Query because you need for it to be valid for many records in a different table, then I would suggest creating a 1 record table and connect it to the records in the Table you are trying to Average as a Parent, and then you can use the Average Summary Field Function, and then you can use a Formula Query to retrieve that single value. If you need it in the Child Table of that then you can just use a Lookup field.

Good Luck!John

------------------------------

John Crosland

Inglett & Stubbs

------------------------------- JosephGomezQrew MemberThanks, John. I knew I just need a bit of guidance to get it to work. Appreciate it.

------------------------------

Joseph Gomez

------------------------------