[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MS Access 2000 -  Subtracting End Date from Start Date

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

mbizup earned 400 total points
ID: 16286613
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 16286635
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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 44

Accepted Solution

GRayL earned 1200 total points
ID: 16290314
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

Author Comment

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

ID: 16309511
Glad to help!
LVL 44

Expert Comment

ID: 16311160
Thanks, glad it helped.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

834 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