Correct Formula For Time In Access

[Eranka]

Hi

i used below code to get the sum of total time to display less than 24 hours and more than 24 hours.

=IIf(((Sum([Accepted_Hours]))<24),(Sum([Accepted_Hours])),(Int(Sum([Accepted_Hours])*24) & ":" & ((Sum([Accepted_Hours])*24)-Int(Sum([Accepted_Hours])*24))*60))

it gives me the answer correct answer if the sum of time is less than 24 hours, but when its more than 24 hours its not displaying the correct answer. (Ex: Sum of Time = 30:10 it shows as 6:10).

What correction to be done to the above formula to get the correct answers.

[Minty]

I think you have already been provided with an answer to this is your other threads.
You are trying to display your answer as a time which as you have discovered isn't possible - 30:10 is simply not a valid time. Display it as a string

CStr((Int(Sum([Accepted_Hours])*24) & ":" & ((Sum([Accepted_Hours])*24)-Int(Sum([Accepted_Hours])*24))*60))

[Eranka]

Hi Guys

there is a problem when i use above formula

=CStr(Int(Sum([Accepted_Hours])*24) & ":" & ((Sum([Accepted_Hours])*24)-Int(Sum([Accepted_Hours])*24))*60)

When i add times such as 4hrs+4hrs+4hrs it should be 12hrs, but instead its showing me 11.59.99 (please see the image). any idea why?

[davegri]

This code (and db using the code) will allow correct addition of Hours and minutes existing as string (not date/time) HH:MM



This is the entire code for the database (the form):

Option Compare Database
Option Explicit


Private Sub cmdCalc_Click()
    Dim nHrs As String
    Dim nMins As String
    nHrs = fcnHours(txtTime1) + fcnHours(txtTime2) + fcnHours(txtTime3)
    nMins = fcnMinutes(txtTime1) + fcnMinutes(txtTime2) + fcnMinutes(txtTime3)

    Select Case Val(nMins)
        Case Is >= 60
            nHrs = (nHrs) + Int((nMins / 60))
            nMins = (nMins Mod 60)
    End Select
    Select Case Len(nMins)
        Case 0
            nMins = "00"
        Case 1
            nMins = "0" & nMins
    End Select
    txtTimeTot = nHrs & ":" & nMins
End Sub


Function fcnHours(argHrs)
    If InStr(argHrs, ":") = 0 Then
        MsgBox "Incorrect time format, use HH:MM", vbOKOnly, "  C H E C K   I N P U T   F O R M A T   "
        Exit Function
    End If
    fcnHours = Val(Left(argHrs, InStr(argHrs, ":") - 1))
End Function


Function fcnMinutes(argMins) As Long
    Dim lMin As Long
    If InStr(argMins, ":") = 0 Then
        MsgBox "Incorrect time format, use HH:MM", vbOKOnly, "  C H E C K   I N P U T   F O R M A T   "
        Exit Function
    End If
    lMin = Len(argMins) - InStr(argMins, ":")
    fcnMinutes = Val(Right(argMins, lMin))
    End Function

If you would rather see the working database itself, look here:
TimeElapsed-v1.zip

[Eranka]

Hi

I guess for a form your code would work. But i want to display the result in a report. can i use your code with a report?

[Minty]

When i add times such as 4hrs+4hrs+4hrs it should be 12hrs, but instead its showing me 11.59.99 (please see the image). any idea why?

Rounding errors - that the problem with doing a mathematical conversion like the one you have used.

davgri's solution is a better way - and yes it will work on a report.