Calculating Average Length of Time

[Stowslee]

I have a query which when run calculates the average length of time, which works most of the time. However there are a few instances in which it is far from accurate, usually with overnight time spans such as 6pm to 3am.

The times are entered and stored in a medium time format. 10:00AM as an example.

The calculation that obtains the actual length of time is:

IIf([End_Time]<8,([End_Time]+24)-[Start_Time],[End_Time]-[Start_Time])

And returns the following as the length of time.

1/22/1900 7:30:00 AM
1/23/1900 8:15:00 AM
1/22/1900 9:30:00 AM

Would be translated into 7 and 1/2 hours.

I have a feeling that how this calculation is made is the cause of the error in its ability to accurately calculate the average time frames.

Any suggestions?

 

[plog]

You need to use the DateDiff() function (http://www.techonthenet.com/access/functions/date/datediff.php) when trying to determine the amount of time between two datetimes.

 

[spikepl]

Time is stored as double, an offset from Dec 30,1899 as the integer part, and the fraction of the day, http://support.microsoft.com/kb/210276 so your iif always executes the second part ,and that's why you get weird (for humans) results.

You need to add 1 to the datetime to indicate a following day. Note that 1 is an integer, meaning one day. And then use DAteDIff as plog says.

 

[Stowslee]

I will play around with that and see what I come up with. Thanks for the quick replies!