MS Access 2000 -  Subtracting End Date from Start Date

Posted on 2006-03-24
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.

Question by:Lou Dufresne
    LVL 61

    Assisted Solution

    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
    LVL 119

    Assisted Solution

    by:Rey Obrero
    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])
    LVL 61

    Expert Comment

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

    Accepted Solution

    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
    LVL 1

    Author Comment

    by:Lou Dufresne
    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.

    LVL 61

    Expert Comment

    Glad to help!
    LVL 44

    Expert Comment

    Thanks, glad it helped.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now