Averaging calculated time fields

[JonathanT]

I have a calculated time field on a report that holds a duration of time based on the start and end time of each record. I'm trying to average the durations for several records. For instance if my durations are 0:26, 0:25, and 1:03, this should average to 0:38. I'm stumped. I would like to do this on a report but I can also do this on a query if necessary

[pbaldy]

Try

=Avg(DurationFieldName)

in the report or group footer. Format the textbox as short time.

[June7]

How do you derive those values?

You need to calculate decimal hour and use that to aggregate.

[JonathanT]

I have a start time and and end time on each record. The difference between the two is my duration field. I use DateDiff("n",[starttime],[endtime])/60 to calculate the duration field to a decimal number field called [timediff] However when I try to either sum or average the [timediff] field in the header it's blank. Any ideas??

[orange]

Did you try Paul's suggestion in post #2?

[pbaldy]

FYI, that DateDiff() would need to be in the source query. You can't Sum, Avg, etc a calculated control on a form/report.

[JonathanT]

I did and the result was wrong

[JonathanT]

I'll try to do the calculation the query. I've been trying to use the report header

[pbaldy]

If you still have trouble, something more than "the result was wrong" would help. Perhaps a sample db.

[June7]

The complete expression must be in the aggregate calc. In textbox in report header:

=Avg(DateDiff("n",[starttime],[endtime])/60)

The advantage of doing aggregate calc in textbox is it allows you to include raw data records in report.

[JonathanT]

Placing the DateDiff calculation in the report header did produce a result but not the correct one. The Duration values I'm trying average are decimals of time, .43, .42, 1.05, 0, 0. The average including the zeros is .38. If the zeros are not included the average is .63. The result I'm getting is .22. The duration field is formatted as Standard and the filed I'm using to average is formatted as Short Time.

[June7]

Why are you not using the duration calc in the Avg? What is the other field?

[JonathanT]

I'm going to start over. I haven't clearly explained my issue.

1. I have a start time and and end time in each record. I have a calculation in a parameter query that calculates the difference between the two and converts it to decimal time. The field is called Duration. The result in the Duration field is also dependent on another field called DateWindow that has either a positive value or zero. If the DateWindow value is zero the the value in the Duration field is zero. If DateWindow has a value other than zero the the result in Duration is the difference between the start and end time. I use an IIF statement with the DateDiff function calculate the value in Duration. The Duration field in the query query returns the correct amount in decimal format.

2. Now I want to to average the values in the Duration field on a report. Using =Avg([Duration]) in the report header does not return the correct result. My values in Duration are .43, .42, 1.05, 0, 0. The average including the zeros is .38. If the zeros are not included the average is .63. The result I'm getting is .22. The Duration field is formatted as Standard and the filed I'm using to average is formatted as Short Time.

3. How do I get the right average result in the header?

[davegri]

Duration should not be formatted as time. Conceptually it is time, but it is actually just a number, a count of seconds or minutes, etc.
If the datediff is giving you seconds and you want to display minutes for example, you'll have to calculate the minutes, seconds / 60.

The date/time format is for a point in time, not a duration of time.

[June7]

I still don't understand what 'field' is formatted as ShortTime. Do you mean 'textbox'? I also don't understand how ShortTime would show .22. Shows 12:00 for me.

Formatting duration won't work. Duration is a quantity (with hours as the unit of measure), not time. Showing the quantity as hh:nn:ss will require another calc to convert to a string. Fairly common topic.