difference between two dates

[frederik]

Hi,
a simple problem, but I didn't find the answer via google.

I have two dates and want to make the difference:

begin = Now()
 
'some other code
 
end = Now()
 
diff = end - begin

The result is saved in a table in the following format: hh:mm:ss
for example: 00:01:03

So far so good. But when there are more than 24 hours between begin and end the difference is for example:
31/12/1899 13:34:36

How can I make the format look good, so it will show 37:34:36 ?

thanks in advance
frederik

 

[dkinley]

Have you tried the DateDiff fuction? ...

DateDiff("h",Begin,End)

-dK

 

[frederik]

Thanks for the hint

I tried this function, and indeed, it show the number of hours correctly, for example 37. (format hh)

But to return on my initial question, how can I have the format look like hh:mm:ss, for example 37:34:36 ?

I tried to reconstruct this using datediff and timeserial as follow:

TimeSerial(DateDiff("h";[start];[stop]);Minute([stop]-[start]);Second([stop]-[start]))

but again, it returns:
31/12/1899 13:34:36
in my access table.

any ideas ?

 

[dkinley]

Okay .. here you go ...

Dim dStart As Date
Dim dEnd As Date
Dim dInterval As Date
 
dStart = DateAdd("h", -2, Now())
dEnd = Now()

dInterval = dEnd - dStart
Me.txtControlName = Int(CSng(dInterval * 24)) & ":" & Format(dInterval, "nn:ss")

-dK

 

[DCrake]

First of all you will need to calculate the number of minutes between the two date/time stamps

M = DateDiff("n",Start,End)

Then use the following function

Function MinsToTime(Mins As Integer) As String
    MinsToTime = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")
End Function