We help IT Professionals succeed at work.

# MS Access 2000 -  Subtracting End Date from Start Date

on
Medium Priority
1,309 Views
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

## View Solutions Only

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.

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
Scrum Master / Technical Business Analyst

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:

Commented:
##### Thanks for using Experts Exchange.

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