Calculating an average when total is 24 hour

[Whitney]

I'm trying to calculating an averge handle time using total number of
processed emails divided by the total handle time. However, since the time
could be longer than 24 hours, I'm using the following expression
=Int(CSng(Sum([Handle Time])*24)) & ":" & Int(CSng(Sum([Handle
Time])*1440))-Int(CSng(Sum([Handle Time])*24))*60 & ":" &
Int(CSng(Sum([Handle Time])*86400))-Int(CSng(Sum([Handle Time])*1440))*60
This is causing the average field to have an error. How can I correct that?

Additionally, I would like the total handle time to be in an hh:nn format,
but it won't save. It keeps changing back to Short Time, which displays
hh:nn:s. Is there any way to correct this?

 

[Klatuu]

What is [Handle Time]? is it a date/time, or an integer, and if it is an
integer, does it carry the time in seconds, minutes, hours?

I can't tell from your code.

 

[Whitney]

It is a field that subtracts one date/time field (Sign In Time) from another
date/time field (Sign Out Time) in a query. It carries the time in hours and
minutes.

What is [Handle Time]? is it a date/time, or an integer, and if it is an
integer, does it carry the time in seconds, minutes, hours?

I can't tell from your code.
--
Dave Hargis, Microsoft Access MVP

I'm trying to calculating an averge handle time using total number of
processed emails divided by the total handle time. However, since the time
could be longer than 24 hours, I'm using the following expression
=Int(CSng(Sum([Handle Time])*24)) & ":" & Int(CSng(Sum([Handle
Time])*1440))-Int(CSng(Sum([Handle Time])*24))*60 & ":" &
Int(CSng(Sum([Handle Time])*86400))-Int(CSng(Sum([Handle Time])*1440))*60
This is causing the average field to have an error. How can I correct that?

Additionally, I would like the total handle time to be in an hh:nn format,
but it won't save. It keeps changing back to Short Time, which displays
hh:nn:s. Is there any way to correct this?

 

[Klatuu]

How do you do that?
The DateDiff function only returns one value, Years, Months, Weeks, Days,
Hours, Minutes, or Seconds. It will not return both.

If you can post the code where you populate the field. And the data type of
the field, I will be happy to help.
--
Dave Hargis, Microsoft Access MVP

It is a field that subtracts one date/time field (Sign In Time) from another
date/time field (Sign Out Time) in a query. It carries the time in hours and
minutes.

What is [Handle Time]? is it a date/time, or an integer, and if it is an
integer, does it carry the time in seconds, minutes, hours?

I can't tell from your code.
--
Dave Hargis, Microsoft Access MVP

I'm trying to calculating an averge handle time using total number of
processed emails divided by the total handle time. However, since the time
could be longer than 24 hours, I'm using the following expression
=Int(CSng(Sum([Handle Time])*24)) & ":" & Int(CSng(Sum([Handle
Time])*1440))-Int(CSng(Sum([Handle Time])*24))*60 & ":" &
Int(CSng(Sum([Handle Time])*86400))-Int(CSng(Sum([Handle Time])*1440))*60
This is causing the average field to have an error. How can I correct that?

Additionally, I would like the total handle time to be in an hh:nn format,
but it won't save. It keeps changing back to Short Time, which displays
hh:nn:s. Is there any way to correct this?

 

[Whitney]

In the query I have the following:
Handle Time: [Sign In Time]-[Sign Out Time]

Example:
Sign In Time 7:00:00 AM - Sign Out Time 6:00:00 AM = Total Handle Time 1:00

Now if I divide that by the number processed 22 = 02:44 (nn:ss) Average
Handle Time per email.

However, when I run the report for a longer date range, the Total Handle
Time exceeds a 24 hour period, so I have to use the expression below to show
total hours. When I use this expression, the Average Handle Time gives an
error. Is there a way to have it reference the sum of the Total Handle Time
and then divide it by the number processed?

How do you do that?
The DateDiff function only returns one value, Years, Months, Weeks, Days,
Hours, Minutes, or Seconds. It will not return both.

If you can post the code where you populate the field. And the data type of
the field, I will be happy to help.
--
Dave Hargis, Microsoft Access MVP

It is a field that subtracts one date/time field (Sign In Time) from another
date/time field (Sign Out Time) in a query. It carries the time in hours and
minutes.

What is [Handle Time]? is it a date/time, or an integer, and if it is an
integer, does it carry the time in seconds, minutes, hours?

I can't tell from your code.
--
Dave Hargis, Microsoft Access MVP


:

I'm trying to calculating an averge handle time using total number of
processed emails divided by the total handle time. However, since the time
could be longer than 24 hours, I'm using the following expression
=Int(CSng(Sum([Handle Time])*24)) & ":" & Int(CSng(Sum([Handle
Time])*1440))-Int(CSng(Sum([Handle Time])*24))*60 & ":" &
Int(CSng(Sum([Handle Time])*86400))-Int(CSng(Sum([Handle Time])*1440))*60
This is causing the average field to have an error. How can I correct that?

Additionally, I would like the total handle time to be in an hh:nn format,
but it won't save. It keeps changing back to Short Time, which displays
hh:nn:s. Is there any way to correct this?

 

[John Spencer]

Use the DateDiff function

DateDiff("s",[Sign In Time],[Sign out Time])

The average would be
Avg(DateDiff("s",[Sign In Time],[Sign out Time]))

To DISPLAY that in minutes and seconds
Avg(DateDiff("s",[Sign In Time],[Sign out Time])) is Seconds

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60
is minutes

So putting that all together.

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60 & ":" &
Format(Avg(DateDiff("s",[Sign In Time],[Sign out Time])),"00")

Total handle time
Sum(DateDiff("s",[Sign In Time],[Sign out Time]))

Expressing that as hours and minutes

Sum(DateDiff("s",[Sign In Time],[Sign out Time]))\3600 & ":" &
Format((Sum(DateDiff("s",[Sign In Time],[Sign out Time])) Mod 3600)
\60,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

In the query I have the following:
Handle Time: [Sign In Time]-[Sign Out Time]

Example:
Sign In Time 7:00:00 AM - Sign Out Time 6:00:00 AM = Total Handle Time 1:00

Now if I divide that by the number processed 22 = 02:44 (nn:ss) Average
Handle Time per email.

However, when I run the report for a longer date range, the Total Handle
Time exceeds a 24 hour period, so I have to use the expression below to show
total hours. When I use this expression, the Average Handle Time gives an
error. Is there a way to have it reference the sum of the Total Handle Time
and then divide it by the number processed?

 

[Douglas J. Steele]

Shouldn't that be

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60 & ":" &
Format(Avg(DateDiff("s",[Sign In Time],[Sign out Time])) Mod 60,"00")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Use the DateDiff function

DateDiff("s",[Sign In Time],[Sign out Time])

The average would be
Avg(DateDiff("s",[Sign In Time],[Sign out Time]))

To DISPLAY that in minutes and seconds
Avg(DateDiff("s",[Sign In Time],[Sign out Time])) is Seconds

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60
is minutes

So putting that all together.

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60 & ":" &
Format(Avg(DateDiff("s",[Sign In Time],[Sign out Time])),"00")

Total handle time
Sum(DateDiff("s",[Sign In Time],[Sign out Time]))

Expressing that as hours and minutes

Sum(DateDiff("s",[Sign In Time],[Sign out Time]))\3600 & ":" &
Format((Sum(DateDiff("s",[Sign In Time],[Sign out Time])) Mod 3600)
\60,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

In the query I have the following:
Handle Time: [Sign In Time]-[Sign Out Time]

Example:
Sign In Time 7:00:00 AM - Sign Out Time 6:00:00 AM = Total Handle Time
1:00

Now if I divide that by the number processed 22 = 02:44 (nn:ss) Average
Handle Time per email.

However, when I run the report for a longer date range, the Total Handle
Time exceeds a 24 hour period, so I have to use the expression below to
show total hours. When I use this expression, the Average Handle Time
gives an error. Is there a way to have it reference the sum of the Total
Handle Time and then divide it by the number processed?

How do you do that?
The DateDiff function only returns one value, Years, Months, Weeks,
Days, Hours, Minutes, or Seconds. It will not return both.

If you can post the code where you populate the field. And the data
type of the field, I will be happy to help.

 

[Whitney]

Ok, the total handle time worked, however the average is not showing
correctly. It should to reference [Processed]. So Average Handle Time:
[Total Handle Time]/[Processed], however that doesn't work. I'm getting
#Error.

Shouldn't that be

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60 & ":" &
Format(Avg(DateDiff("s",[Sign In Time],[Sign out Time])) Mod 60,"00")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Use the DateDiff function

DateDiff("s",[Sign In Time],[Sign out Time])

The average would be
Avg(DateDiff("s",[Sign In Time],[Sign out Time]))

To DISPLAY that in minutes and seconds
Avg(DateDiff("s",[Sign In Time],[Sign out Time])) is Seconds

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60
is minutes

So putting that all together.

Avg(DateDiff("s",[Sign In Time],[Sign out Time]))\60 & ":" &
Format(Avg(DateDiff("s",[Sign In Time],[Sign out Time])),"00")

Total handle time
Sum(DateDiff("s",[Sign In Time],[Sign out Time]))

Expressing that as hours and minutes

Sum(DateDiff("s",[Sign In Time],[Sign out Time]))\3600 & ":" &
Format((Sum(DateDiff("s",[Sign In Time],[Sign out Time])) Mod 3600)
\60,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

In the query I have the following:
Handle Time: [Sign In Time]-[Sign Out Time]

Example:
Sign In Time 7:00:00 AM - Sign Out Time 6:00:00 AM = Total Handle Time
1:00

Now if I divide that by the number processed 22 = 02:44 (nn:ss) Average
Handle Time per email.

However, when I run the report for a longer date range, the Total Handle
Time exceeds a 24 hour period, so I have to use the expression below to
show total hours. When I use this expression, the Average Handle Time
gives an error. Is there a way to have it reference the sum of the Total
Handle Time and then divide it by the number processed?

:

How do you do that?
The DateDiff function only returns one value, Years, Months, Weeks,
Days, Hours, Minutes, or Seconds. It will not return both.

If you can post the code where you populate the field. And the data
type of the field, I will be happy to help.