Finding Average of Time.

[Reg]

I have a column which has Time like: 1:24, 0:25,0:21,2:00 ......
I am trying to get a average from such calculated time.
=AVG([column]) is not giving me the right average..

Can someone Please Help? i am a newbie to Access just a week old..

 

[Jeff Boyce]

Reg

First, is the underlying field a Date/Time type field or a text field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[S.Clark]

Assuming that 1:24 means 1 hour and 24 seconds....

I would break down the times to be all seconds, so 1:24 would be 84 seconds.

Then take the average of that column, convert back to pretty as needed.

 

[Reg]

The underlying field is a Date/Time field.

Reg

First, is the underlying field a Date/Time type field or a text field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a column which has Time like: 1:24, 0:25,0:21,2:00 ......
I am trying to get a average from such calculated time.
=AVG([column]) is not giving me the right average..

Can someone Please Help? i am a newbie to Access just a week old..

 

[Reg]

So make a another column in the query to convert the values to sec.Which
function will i use to do such convertion.
And once Converted , Average the sec. out and back to mm:ss.. Is that correct?

Assuming that 1:24 means 1 hour and 24 seconds....

I would break down the times to be all seconds, so 1:24 would be 84 seconds.

Then take the average of that column, convert back to pretty as needed.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

I have a column which has Time like: 1:24, 0:25,0:21,2:00 ......
I am trying to get a average from such calculated time.
=AVG([column]) is not giving me the right average..

Can someone Please Help? i am a newbie to Access just a week old..

 

[Jeff Boyce]

Access Date/Time fields actually contain both a date portion and a time
portion, and represent a "point-in-time".

As such, it makes no sense to trying to "average" these values.

You'll need to record your time (i.e, a duration, right?) in a text field if
you wish to use the hh:mm format you mentioned.

Then, as Steve mentions, you'll have to determine the total time in the
lowest term (?minutes, ?seconds). How would you explain doing this to a
person helping you with the calculations? That's how you'd explain it to
Access.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The underlying field is a Date/Time field.

Reg

First, is the underlying field a Date/Time type field or a text field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a column which has Time like: 1:24, 0:25,0:21,2:00 ......
I am trying to get a average from such calculated time.
=AVG([column]) is not giving me the right average..

Can someone Please Help? i am a newbie to Access just a week old..

 

[Jeff Boyce]

Dale

That sounds like a perfectly workable solution.

I am a self-confessed data bigot, so the thought of putting "duration"
values in a field designed for "point-in-time" just rubs me the wrong way
(sorta like folks who put "numbers" in a text field and then complain when
they don't sort "properly"!<g>).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

While I understand that it doesn't necessarily make sense to store
durations
in Date/Time fields, that doesn't make it "necessary" to use a text
format.
For example, using:

SELECT AVG(TimeValue([Duration])) as AvgDuration
FROM hisTable

Would return a decimal value that averages the duration. This value would
obviously be a percentage of a day, but could then be converted into
"hh:mm:
ss" format as:

SELECT Format(Avg(TimeValue([Duration])),"hh:nn:ss") AS AvgDuration
FROM hisTable

Dale

Access Date/Time fields actually contain both a date portion and a time
portion, and represent a "point-in-time".

As such, it makes no sense to trying to "average" these values.

You'll need to record your time (i.e, a duration, right?) in a text field
if
you wish to use the hh:mm format you mentioned.

Then, as Steve mentions, you'll have to determine the total time in the
lowest term (?minutes, ?seconds). How would you explain doing this to a
person helping you with the calculations? That's how you'd explain it to
Access.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The underlying field is a Date/Time field.

[quoted text clipped - 12 lines]

Can someone Please Help? i am a newbie to Access just a week old..