average time for records

[jjb373]

I want to get the average time for my "Late External Documents" field in my report footer.

To get the "Late External Documents" time, I used the DateDiff function. My two date fields used in the DateDiff function are: "Appointment Time" and "External Documents" recieved time.

The results format for the "Late External Documents" field looks like this: 2d 15hrs 25min

Example of the report and what I want:

number Late Extrn Docs
1 2d 15hrs 25min
2 1d 21hrs 0min
3 0d 15hrs 55min
4 0d 2hrs 31min
5 1d 0hrs 15min
6 3d 0hrs 0min

---------------------------------
report footer
AVG: (average time for all 6 records)

I hope this is clear. Thanks in advance!

 

[dhookom]

This expression will give you the average in seconds. If you want another time increment, change the "s" to something else or divide the result by some number.
=Avg(DateDiff("s",[Appointment Time],[External Documents]))


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jjb373]

That works great, but I would like the format to display:

_days _hours _mintues

What do I need to do to that expression to display the answer in this format instead of just seconds...

Thanks for your help!

 

[dhookom]

How do you get

The results format for the "Late External Documents" field looks like this: 2d 15hrs 25min

I expect you can take your seconds and use the same basic method.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jjb373]

I use this formula:

External Late: IIf(Documents![External Documents]-Documents![Date of Appointment]>1,Int(Documents![External Documents]-Documents![Date of Appointment]) & 'd ' & Format(Documents![External Documents]-Documents![Date of Appointment],'h"h "n') & 'min','')

 

[dhookom]

Use the same calculation except replace:
[blue]Documents![External Documents]-Documents![Date of Appointment][/blue]
with
[green]Avg(DateDiff("n",[Appointment Time],[External Documents]))/1440[/green].

This expression averages the number of minutes divided by the number of minutes in a day.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]