How to Average time in MS Access 2003

[sossier]

I need to Average time data that has been entered. What do I need to do in the query?

[Rabbit]

Use the average function: Avg().

[sossier]

I know that. But when I Avg date/time entries I get a very low number (0.291666666666 667). is there something I need to put in the criteria to make it show correctly?

[Rabbit]

Use CDate() to convert the number to a date time value.

[zmbd]

You need to be more specific as to how your data is being entered.
Say I Work from 8am to 4pm every day I average 8 hours a day; however, to calculate that information depends on how the information is stored in the data tables. If it is simply stored in a field type cast numeric(double) 8.0 then you can simply use an agregate query to do the average. However, if you are storing the information in date/time fields then you need to calculate the time difference and then the average.

This sounds very much like a homework question, we try not to provide code for such; thus, you will need to post you SQL either for the calculated field or the entire SQL script.

Please remember to format such script using the [CODE/] button.
(^_^)

[NeoPa]

Converting the form of the data (Using CDate()) does nothing to the data itself (in this case), but it will have the effect of telling the SQL engine to change the default format of the data to a date/time based one. Although this (or something very similar) can also be achieved by using the Format property of the column, or even the Format() function itself, these other approaches have limitations in some scenarios.

What you are seeing is that date/time data is being passed into the Avg() function but as the return value for this function is not, itself, defined as date/time, the SQL engine treats it as a general number.

[sossier]

so is there anyway to avg a series of time data? data inputed as date/time. I am inputing the actual time a task is started and then in another field the time it was finished. So we want to avg all the start times and avg all the finished times.

[Rabbit]

Use DateDiff to calculate the different in minutes between the two tasks and then average that value. That will give you the average number of minutes it took to complete a task.

[sossier]

I dont need to calculate the avg number of minutes it takes to do the task. We are calcuating the average of the actual time.

for example 12/1 7:00am
12/2 7:15am
12/3 7:20am
12/4 7:00am

then figure out what the avg time for those 4 values.

[zmbd]

So you want what?
07:09 AM
or 9 minutes
or what?

[sossier]

7:09 AM is what I am looking for

[zmbd]

And this is a beautiful homework question.

Rabbit in post #8 has the first part of the answer for you.

Once you have the average elapsed minutes then add that to the root time (lowest hour)

Please post your code/SQL, if you would, kindly format the posted script using the [CODE/] button.

[Rabbit]

If you want an average starting time, then you will need to 0 out the integer part of the date so that it all starts on the same date with only the time varying. Then you can take the average of that.

You want to 0 out the interger part of a date because the a date is stored as a decimal with the fraction representing time and the integer representing number of days elapsed.

[NeoPa]

Another way to do that is to use TimeValue() of the start date/times within the Avg() function call.

NB. It would have been much easier to give a proper answer to your question right from the off if you'd taken the effort to ask your question properly at the start. Your first post was not even half of the story we subsequently learned. Please do not follow this pattern in future questions. If so, they are likely to be deleted immediately before so many people's time is wasted on getting to the point that should be the start point.