Sum Time Problem

[robcarr]

Dear All,

I have report that shows how long the agents are logged into the phones if the agents total time is greater than 24hrs,it resets, how can I get the report to show total hours correctly, I have one agent who has worked.
08:55:19
07:15:19
08:55:19
total shows as 01:05:57, but should be 25:05:57.

Thanks

Thanks Rob.

 

[jrbarnett]

Hi,

The Access time controls don't calculate time over 24 hours correctly - they add 1 day.
My solution is to convert the total time to minutes
(formula is Hours(Time)*60 + Minutes (Time))

then total the minutes, and calculate them back in a small function:

NewHours = Total Minutes \ 60
NewMinutes = Total Minutes Mod 60
and then use string formatting to make it look like a total:

Format (NewHours, "00&quot & ":" & Format (NewMinutes, "00&quot

then align it to the right. As long as no calculations are performed on this total, it works great.

John

 

[dhookom]

Date/time fields are best used to store points in time rather than durations of time. You can use something like

Int(YourTime * 24) & ":" & Format(YourTime,"nn:ss&quot

Duane
MS Access MVP

 

[robcarr]

thanks all fot your help on this, I am still getting very confued and lost with this, I can not get this to work properly, I am new to Access, and dont understand it that well yet, can someone explain to me exactly how an where to enter this in, as I am getting more and more lost wit it.

Thanks for any help.

Rob.

Thanks Rob.

 

[PaulBricker]

Rob, you can try this in the Control Source for your textbox on the Report.
=Int(Sum([DateField]))*24+DatePart("h",Sum([DateField])) & ":" & DatePart("n",Sum([DateField])) & ":" & DatePart("s",Sum([DateField]))

Paul

 

[dhookom]

Make sure the name of the text box is not also the name of a field in your report's record source.

Duane
MS Access MVP