Calculating Time (I'm desparate) (1 Viewer)

[camelk]

This is my 2nd post. The initial suggestion was so confusing. I need to calculate the total time worked for a particular individual on a daily basis. This needs to be in hours and minutes. Then I need to get each day's total and add them up over a several day period. I used DateDiff to get the daily difference between EndTime and StartTime, but it would not let me enter "nn" for minutes (the times are formatted as short). Here's the code that I used. It gave the correct number of hrs but wouldn't let me add minutes.
Tot: Sum(Int(DateDiff("[StartTime], [EndTime]))) Then I need code to add up all the daily totals. Can someone please help?

 

[SimonC]

I think your best bet would be to do as much of your calculations as you can in minutes rather than trying to use datetime fields to hold time periods which, when summed, will exceed 24 hours.

From your start and end time, calculate the number of minutes using DateDiff("n",[StartTime],[EndTime]).

Wherever you need to show the number of hours and minutes, on your form or report, use a custom function to format the minutes into hours and minutes. It shouldn't need to be much more complicated than this:

Public Function DisplayTime(ByVal Minutes As Variant)
If IsNull(Minutes) Then
DisplayTime = Null
ElseIf Not IsNumeric(Minutes) Then
DisplayTime = Null
Else
DisplayTime = Format(Minutes \ 60, "00") & ":" & Format(Minutes Mod 60, "00")
End If
End Function

 

[camelk]

Thanks. This worked perfectly.