MS Access "Hours" Calculation Help Needed

[AccessRookie]

Hi,

I have a report that I am calculating Hours and Minutes for IT help desk time spent resolving various users problems. It is broken down by Department with calulcations per Dept and then a Grand Total Sum of Hours and Minutes at the end.

My problem - I calculate a revolving Sum based on date ranged entered per department.
I am using this in the Department Footer to calculate Minutes: =Sum([Minutes]) Mod 60 - it works fine.

However for HOURS - if the Sum of Minutes is under 60 minutes it is rounding up to an Hour making the SUM incorrect by this inaccurate Hour. For example, if Accounting had 0 Hours and 50 Minutes of IT Help Desk Time Used - the sum shows Accounting having 1 Hour.

Here is the formula I am using for HOURS:
=Sum(([Hours])+([Minutes])/60)

Please help!! First correct answer received 300 points. Thanks in advance!

 

[Crowley16]

erm, have you considered using a single time value, and then reformatting it to show hours:mins?

but if you can't do that, then why not just sum the minutes, and then use that to calculate the hours + mins seperately...

you can put hours in it's own text box, and then set the number of decimals to show = 0, which will give you the correct no. of hours, and use the mod 60 for the minutes, hope that makes sense

--------------------
Procrastinate Now!

 

[AccessRookie]

but if you can't do that, then why not just sum the minutes, and then use that to calculate the hours + mins seperately..."

That is what I currently have, but when you add your hours and (minutes/60) - if the minutes total is under 60 minutes it rounds up as 1 - and thus adds an incorrect Hour.

for example - if Accounting only used 50 minutes of IT time - the report displays them as 1 hour. If Admin Dept used 35 minutes - it will display them as one hour also.

Thanks for any help

 

[PaulBricker]

I would check to make sure you don't have the textbox formatted as Integer.
If that isn't the problem, it might help to actually post a couple examples of the data structure on the Report and the expression you use to sum everything in your textboxes.



Paul

 

[AccessRookie]

The formula I am using for Hours:
=Sum(([Hours])+([Minutes])/60)


But the problem is any minutes under 60 get calculated as 1 hour. It is rounding I guess.

 

[AccessRookie]

Anyone have any other suggestions?

 

[PaulBricker]

I did see your expression in the original post, but I'm having trouble picturing exactly how your fields in the Report are laid out. Do you have two fields, Hours and Minutes, or one field with Hours and Minutes in it. In another words, is it HH:MM in a single field or HH in one field and MM in another? That's the data I think we need to find a solution.


Paul

 

[tpsmith]

Hi

I wrote something similar which you may be able to work with that adds seconds onto a time, if it goes over 24 hours it displays it correctly.

Hope it helps

Function Time_Add(Time1 As Date, Dur_Secs As Double) As Variant
'Written by TS 20/12/2004
On Error Resume Next

Dim ds1 As Double
Dim ds2 As Double
Dim daytxt As Variant

'Check to see if the total duration is longer than 1 day,
'if it is then split the 24 hour clock into number of days.
If Dur_Secs > 86399 Then
ds1 = Int(Dur_Secs / 86399)
ds2 = Dur_Secs - (ds1 * 86399)
Time_Add = DateAdd("s", ds2, Format(Time1, "hh:nn:ss"))
Else
'If less than a day then just display the time
Time_Add = DateAdd("s", Dur_Secs, Format(Time1, "hh:nn:ss"))
End If

'This section just formats the results for the Report.
If ds1 = 0 Then
Time_Add = Time_Add
ElseIf ds1 = 1 Then
Time_Add = ds1 & " day " & Time_Add
Else
Time_Add = ds1 & " days " & Time_Add
End If

End Function

 

[AccessRookie]

Thanks for the replies - Paul in regards to your question, I am campturing Hours in one field and Minutes in another. When I total them on the same report, if minutes are under 60 - it adds an hour to the hour field in the report.

 

[ssecca]

What are your field types? (ie. Real, double, integer, etc.)