Help on Calculating time 1

[jen1701]

I am trying to time calculation using DateDiff.
ie. 2:07:34pm - 9:33:25am = 4 hours 33 mins and 09 seconds.

I used following code:

Days = DateDiff("d", Me![AuditStartDate], Me![AuditEndDate])
Hours = DateDiff("h", Me![AuditStartDate], Me![AuditEndDate])
Hours = iHours - (Days * 24)
Minutes = DateDiff("n", Me![AuditStartDate], Me![AuditEndDate])
Minutes = Minutes - (iHours * 60)
Me![Audit Time] = iDays & " days, " & iHours & " hours, " & Minutes & " minutes, "

but I got the result is 0 days 5 hours -26 minutes.
Can someone give me some advise on this? Thanks a lot in advance.

 

[FancyPrairie]

Try this:

Function DisplayElapsedTime()

    Dim lngDays As Long
    Dim lngHrs As Long
    Dim lngMins As Long
    Dim lngSecs As Long
    Dim lngTotalHrs As Long
    Dim lngTotalMins As Long
    Dim lngTotalSecs As Long
    
    Dim dteStart As Date
    Dim dteEnd As Date
    
    Dim dblInterval As Double
    
    
    dteStart = CDate("9:33:25am")
    dteEnd = CDate("2:07:34pm")
    
    dblInterval = Abs(dteEnd - dteStart)
    lngDays = Int(CSng(Abs(dteEnd - dteStart)))

    lngTotalHrs = Int(CSng(dblInterval * 24))
    lngTotalMins = Int(CSng(dblInterval * 1440))
    lngTotalSecs = Int(CSng(dblInterval * 86400))
    
    lngHrs = lngTotalHrs Mod 24
    lngMins = lngTotalMins Mod 60
    lngSecs = lngTotalSecs Mod 60

    MsgBox lngDays & " days " & lngHrs & " hours " & lngMins & " minutes " & lngSecs & " seconds"
    

End Function

 

[PHV]

And something like this ?
Me![Audit Time] = Int(Me![AuditEndDate] - Me![AuditStartDate]) & " days, " _
& Format(Me![AuditEndDate] - Me![AuditStartDate]," H \hour\s, N \mi\nute\s")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

PHV,

An interesting (if obscure) use of the format capability.

jen1701,

A similar but somewhat more easily understood (by humans at least) is close to what you are asking for:

AuditTime = int(AuditEndDate - AuditStartDate) & " days, " & Format(AuditEndDate - AuditStartDate,"hh:mm:ss")

Although not specifically spelling out the words, the format is commonly understood.





MichaelRed
mlred@verizon.net

 

[jen1701]

Thank you so much for FancyPrairie, PHV and MichaelRed's replies. I tried your suggestions and worked well. Thank you again for your help. Now I have another problem. I have many records of audittime. I need to add them together to print it out as Total audit time per job in a report. But I can not use the result of 1days, 4:23:12 to add them up. I assume I still need to use datediff("h" or "n" or "s",Me![AuditStartDate], Me![AuditEndDate]) on each audit record and then I need add them up. Final I will convert from the total hours or mins into 1days, 9hours, 29 mins. I am not sure this is the right way to do it. Is there any better suggestions? Thanks.

Jen


Jen

 

[PHV]

Have you tried something like this ?
Int(Sum([AuditEndDate] - [AuditStartDate])) & " days, " _
& Format(Sum([AuditEndDate] - [AuditStartDate])," H \hour\s, N \mi\nute\s")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

Oh LAKDJFNPOU*y$)#(*&$)(*!@#w(!*@& !!!
i LEFT OUT THE MOST IMPORTANT advice for these low lwvwl issues!!!

See the ubiquitous {F1}[/color red]. This is NOT a baby sitting or help desk site. We are supposedly all "professionals" and generally expect those we offer help/aid/comfort etc to will use at least SOME of their own effort in understanding the results. I, perhaps, should have refained from even posting to begin with, but felt than PHV's post was sufficiently obscure to instantiate confusion. I see that I was abjectly wrong and apologise for the intrusion. His post is RIGHT ON TARGET.





MichaelRed
mlred@verizon.net