We help IT Professionals succeed at work.

MS Access 2000 -  Subtracting End Date from Start Date

Lou Dufresne
Lou Dufresne asked
on
Medium Priority
1,309 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2016
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])
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

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

http://www.techonthenet.com/access/questions/elapsed.php
Commented:
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
Lou DufresneScrum Master / Technical Business Analyst

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad to help!

Commented:
Thanks, glad it helped.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.