date interval help

  • Thread starter Thread starter Access
  • Start date Start date
A

Access

HI

Here is what I have

[etime1] = date. ex 10/31/03
[etime2] = time. ex 14:10

I need to calculate the time interval between now() and the future date/time
and get the results in hh:nn (hours/minutes). Like this:

now() - [etime1]&" "&[etime2] = 600:25

but how can I do it....

HELP!
 
What you have to do depends on the Field type or Variable type of etime1 and
etime2. If they are defined as Date/Time fields, add them together and use
them directly in a DateDiff function. Example:

Function DD(etime1 As Date, etime2 As Date, RefDate As Date) As String
Dim mm As Long
Dim hh As Long
Dim rm As Long
mm = DateDiff("n", RefDate, etime1 + etime2)
hh = mm \ 60
rm = mm Mod 60
DD = Format(hh, "00") & ":" & Format(rm, "00")
End Function

and in the Immediate Window:

? DD(#10/07/03#,#2:00:00 PM#,#10/06/03 01:35:00 PM#)
24:25

You could, of course, combine some of the steps I showed just for clarity,
as I did below.

If they are defined as text, convert them to DateTime with CDate, then add
them together and use them in a DateDiff function. Try this example in the
Immediate/Debug Window:

? DateDiff("n",Now(),CDate("10/07/03")+CDate("2:00 PM"))



Larry Linson
Microsoft Access MVP
 
Back
Top