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
LVL 1
Lou DufresneIT Business Analyst CSM / Project ManagerAsked:
Who is Participating?
 
GRayLCommented:
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
0
 
mbizupCommented:
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
0
 
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 _
     Long
 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])
0
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.

 
mbizupCommented:
Also check this function for ful Years, days, hours, minutes:

http://www.techonthenet.com/access/questions/elapsed.php
0
 
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.

Lou
0
 
mbizupCommented:
Glad to help!
0
 
GRayLCommented:
Thanks, glad it helped.
0
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.