Date Difference in d hh:nn:ss in Aceess

[Moor]

Hi,

I am a beginner to MSAccess. Recently I imported a huge file contains
data about Telco providers outage timings as follows:

Following are the three columns from excel.

"Problem Start Time ", "Problem End Time" and "Duration", duration is
the difference between the first two columns.

It was all ok when these fields are at excel as I have to display them
in d:hh:mm format which is days:hours:minutes. Pls. take a look at the
exact format below.

Start Time End Time Duration
m/d/yyyy h:mm - m/d/yyyy h:mm = d:hh:mm
5/23/2006 16:19 - 5/25/2006 11:21 = 1:19:02

Pls. help me to find a way to display in the format of d:hh:mm when I
use them in MS Access for to store in tables and manipulate thru
queries and calculations.

Thanks in advance

Moor

 

[Jeff Boyce]

We can simplify your task right away ... don't store the duration value. If
your database will already hold start and end date/times, use a query to
calculate duration whenever you need it.

Have you tried creating a query to do that calculation, then using the
Format() function to display the results as you wish to see them?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Shelby]

I am trying to do the same thing and I have also been researching this a bit
for the last few days. I haven't found anything that gives me a clear answer
to this question. I can use the following DateDiff formula to retrieve days,
hours, minutes (as listed below) but not all three at one time.

DateDiff("d",[start],[end]) = Days
DateDiff("h",[start],[end]) = hours
DateDiff("n",[start],[end]) = minutes

Please point me/us in the right direction if you can.

Thanks,
Shelby

 

[Klatuu]

As Jeff said, don't store calculated values in a table. Here is a function
that will return the duration in dd:hh:mm format:

Function ShowDuration (dtmStart as Date, dtmEnd as Date) as String
Dim lngDur and Long
lngDur = DateDiff("n", dtmStart, dtmEnd)
ShowDuration = Format(lngDur\1440,"00:") & _
Format((lngDur -(lngDur\1440)*1440)\60,"00:") & _
Format((lngDur -(lngDur\1440)*1440) Mod 60,"00")
End Function