Calculating Time without it rolling over 1

[Guest_imported]

Can someone please tell me how to calculate time in a query, form, and report without it rolling over (e.g when the time totals to 24:00, it does not go back to 0:00(24 hour time)). I have tried several different things and none of them seem to work. Please could someone help me!!!

 

[MichaelRed]

We woulu need some specifics to fully understand your problem. In a general sense, You should use the full date-time data to calculate differences in event times. Use

DateDiff("i]i[/i]", Start, End) Where "i" is the interval in question. See Help on "DateDiff" for details. In most cases, datadiff returns the number of "interval" thresholds crossed, so that the diffference in hours between 9:59 AM and 10:01 AM is 1 Hour, while the difference between 9)01 AM and 9:59 AM is zero!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Guest_imported]

I want to record people doing hours and have a total time for them. I can record their Time In, Time Out and come up with Time Consumed. For Example, Time In = 1:00 PM, Time Out = 3:00 PM, so Time Consumed = 2:00 hours (Using Time Out - Time In). Now if they come back at 4:00 PM and do another 2 hours, I have it so Time Consumed totals 4:00 hours. This part of it works correctly. My problem is once they have done 24:00 hours, Time Consumed total rolls over to 0:00 hours, but I want it to carry on with it totaling (i.e 27:00 hours and so on).

 

[MichaelRed]

For this you will need to accumulate the intervals in a "Double" value (add each of the times - 2Hrs, 3Hrs, 2.1Hrs ...) in this. To get the total, the "Whole Number" portion of your "Double" variable is the Number of days (e.g. 1.0 = 24 Hrs), while the decimal portion is the "Fraction" of a day, as in 1440 Minutes = 24Hrs, or 86,400 Seconds = 24Hrs. So, if you want to 'Calculate' the hrs and minutes, you Multiply the fractional part by 1440 to get the integer Minutes. do an integer divide of this value by 60 to get the hours, and take the modulo 60 of it to get the minutes.

There a a large number of ways to accomplish this, and the following is NOT the recommended approach, but is given to illustrate the math (it should also give a correct answer - but that is incidental).

Public Function basDbl2HrsMin(AccumTime As Double) As String

    Dim tmpDays As Double
    Dim tmpHrs As Double
    Dim tmpMins As Double
    Dim tmpTime As Double
    Dim tmpStr As String

    tmpDays = Int(AccumTime)
    tmpTime = (AccumTime - tmpDays) * 1440      'Fraction to Mins

    tmpMins = tmpTime Mod 60
    tmpTime = tmpTime - Int(tmpMins)
    tmpHrs = tmpTime \ 60

    'formatting for Day(s)
    If (tmpDays) Then
        tmpStr = tmpDays & "Day"
        If (tmpDays > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'formatting for Hour(s)
    If (tmpHrs) Then
        tmpStr = tmpStr & " " & tmpHrs & "Hr"
        If (tmpHrs > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'formatting for Minutes(s)
    If (tmpMins) Then
        tmpStr = tmpStr & " " & tmpMins & "Min"
        If (tmpMins > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If


    basDbl2HrsMin = tmpStr
    

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over