# Correct Formula For Time In Access

1. Competent Performer Windows 10 Access 2016  Join Date
Dec 2017
Posts
150

## Correct Formula For Time In Access

Hi

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

Code:
`=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.  Reply With Quote

2. VIP Windows 10 Access 2010 32bit           Join Date
Sep 2017
Location
UK - Wiltshire
Posts
2,911
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))  Reply With Quote

3. Competent Performer Windows 10 Access 2016  Join Date
Dec 2017
Posts
150
Hi Guys

there is a problem when i use above formula
Code:
`=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?
Last edited by Eranka; 01-09-2018 at 07:02 AM.  Reply With Quote

4. 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):

Code:
```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
Last edited by davegri; 01-10-2018 at 09:09 AM. Reason: layout  Reply With Quote

5. Competent Performer Windows 10 Access 2016  Join Date
Dec 2017
Posts
150
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?  Reply With Quote

6. VIP Windows 10 Access 2010 32bit           Join Date
Sep 2017
Location
UK - Wiltshire
Posts
2,911 Originally Posted by Eranka 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.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums