Return Average based on "time' field

[kentwood]

I have a report that is the result of data imported from Crystal Reports to Excel to Access. In Excel, the Cell is listed as a General Number and formatted like 00:00:00. The first 2 zeros are for Minutes, the second set for Seconds, and the third set for partial seconds, whatever that is called.

I import this to my table, which is formatted as Date/Time, as hh:nn:ss. I don't know of any other way to format this to look as it does above and accept the data I import. Is there another way to show this format?

Anyway, on my report, I need to determine an average for this figure that is calculated for each month. I tried the Avg function and it will not work based on data format. Is it possible to set a formula to sum this up and divide by the number of months? I believe it is, but I cannot find a reference to it in my books or FAQ?

 

[barbarossaii]

Hi kentwood,

could try to sum & divide by the number of counted entries (to circumvent the avg function).
If it doesn't work in the report, try it in a query and show the result via a subreport. Just in case the special formate creates problems: convert ur time in partial seconds add them, calculate the avg & convert back (for conversion use the formate command).

HTH,
Barbarossa II

 

[kentwood]

After posting this question, I did go with the sum feature and that works fine. Then what I could not remember was how to divide by the count of the months. So again, I played and figured it out. My forumula works great and now reads:

=Sum([Fatique])/Count([Date])


Sometimes I surprise myself. Persistance pays off. Thanks for your response.

 

[kentwood]

A different way of approaching it but works great. Thanks for the help.