MS Access 2000 - Subtracting End Date from Start Date

I have a report with StartDate and EndDate fields on a report. I have another field on the report where I am want to subtract the StartDate from the EndDate. The format on the StartDate and EndDate fields is General Date Format.

I want to be able to subtract one from the other to get elapsed time. I tried DateDiff but I cannot get the detail I need ( Hours Minutes) from this function.

I would appreciate any insight.

Lou DufresneIT Business Analyst CSM / Project ManagerAsked:
Who is Participating?
Give this a try:

=Int(datediff("n",d1,d2)/60) & ":" & datediff("n",d1,d2) mod 60

d1 is the earlier datetime.  It gives you an answer like:  12:34
This will give you a string output detailing the time elapsed:

Function timediff(d1 As Date, d2 As Date) As String
    Dim mdiff, hdiff As Integer
    mdiff = Abs(DateDiff("n", d1, d2))                                    ' Total Time elapsed in minutes    
    hdiff = Int(mdiff / 60)                                                     ' Total hours elapsed
    mdiff = mdiff - hdiff * 60                                                ' Minutes difference
    timediff = hdiff & "Hours, " & mdiff & " Minutes"
End Function
Rey Obrero (Capricorn1)Commented:
here is  function to get elapsed time
place this codes in a module

Function GetElapsedTime(interval)

 Dim totalhours As Long, totalminutes As Long, totalseconds As _
 Dim Days As Long, Hours As Long, Minutes As Long, Seconds As Long

 Days = Int(CSng(interval))
 totalhours = Int(CSng(interval * 24))
 totalminutes = Int(CSng(interval * 1440))
 totalseconds = Int(CSng(interval * 86400))
 Hours = totalhours Mod 24
 Minutes = totalminutes Mod 60
 Seconds = totalseconds Mod 60

 ' GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & _
       " Minutes " & Seconds & " Seconds "

 'for hour and minutes just use
 '  GetElapsedTime= Hours  & " Hours " & Minutes & "Minutes"
 '  or
   GetElapsedTime= Hours &":" & Minutes
 End Function

to use the function

 set the controlSource of the  textbox to
     ControlSource: =GetElapsedTime([EndDate]-[StartDate])
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Also check this function for ful Years, days, hours, minutes:
Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Thank you all for your assistance. The code that best fits my circumstances for this project was submitted by GRayL.

Thank you all for your assistance.

Glad to help!
Thanks, glad it helped.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.