time format question

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

Hi there again
You have all help greatly.
here is another ?
in this expression, how can i get it to calculate in hours,minutes and
seconds
=IIf(DateDiff("n",[1 Delay Time In],[1 Delay Time Out]) Between 0 And
45,0,DateDiff("n",[1 Delay Time In],[1 Delay Time Out])-45)
This is the one that is currently working great for minutes only

thank you again
 
Maybe someone can come up with a formula that you can enter into the
Expression Builder, but for me it's much too complex.

However, here's a function you can put in a module and then call from
wherever you want. It takes two variables as input, StartDateTime and
EndDateTime. These are date fields that include both the date and time (I
use short date format but I don't think it matters). It's output is a string
giving the interval in hours, minutes and seconds in hh:mm:ss format (e.g.
1:30:25).

Here's an example of using the function to populate a text box control
called txtInterval with the inputs being two text box controls called
txtStart and txtFinish:

Me.txtInterval = CalcInterval(Me.txtStart, Me.txtFinish)

Hope this helps...

Regards, Chris


'********* Start Code *************
Public Function CalcInterval(ByVal StartDateTime As Date, ByVal EndDateTime
As Date) As String
On Error GoTo ERR_CalcInterval

Dim lngInterval As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

' Interval in seconds
lngInterval = Nz(DateDiff("s", StartDateTime, EndDateTime), 0)

' Subtract the 45 minute delay factor (45 * 60 = 2700)
lngInterval = IIf(lngInterval <= 2700, 0, lngInterval - 2700)

' Calculate hours
lngHours = lngInterval \ 3600

' Calculate minutes
lngMinutes = (lngInterval - (lngHours * 3600)) \ 60

' Calculate seconds
lngSeconds = lngInterval - (lngHours * 3600) - (lngMinutes * 60)

'Now, put it all together
CalcInterval = Trim(str(lngHours)) & ":" & Trim(str(lngMinutes)) & ":" & _
IIf(Len(str(lngSeconds)) = 2, Trim("0" & Trim(str(lngSeconds))),
Trim(str(lngSeconds)))

Exit_CalcInterval:
Exit Function

ERR_CalcInterval:
MsgBox Err.Number, Err.Description
Resume Exit_CalcInterval

End Function
'********* End Code *************
 
LOL!!!! Gee, Alex, did you *have* to do it in one line??? And here I
thought my 20 line function was cool! (Grin!)

Regards, Chris

Alex Dybenko said:
hi,
Like this:

format([1 Delay Time In]-[1 Delay Time Out],"hh:nn:ss")

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

bob said:
Hi there again
You have all help greatly.
here is another ?
in this expression, how can i get it to calculate in hours,minutes and
seconds
=IIf(DateDiff("n",[1 Delay Time In],[1 Delay Time Out]) Between 0 And
45,0,DateDiff("n",[1 Delay Time In],[1 Delay Time Out])-45)
This is the one that is currently working great for minutes only

thank you again
Click to expand...
Click to expand...
 
Of course you can subtract times like that. Date/Time fields are simply 8
byte floating point numbers, where the integer portion represents the date
as the number of days relative to 30 Dec, 1899, and the decimal portion
represents the time as a fraction of a day. Since they're numbers, you can
do any arithmetic you want with them (although I'll grant that it won't
always give you the correct results)

From the immediate window:
?#1:15 PM# - #8:00#
0.21875
?Format(#1:15 PM# - #8:00#, "hh:nn:ss")
05:15:00

Even when there's a date there it works:

?#2008-08-22 13:15:00# - #2008-08-22 08:00:00#
0.21875
?Format(#2008-08-22 13:15:00# - #2008-08-22 08:00:00#, "hh:nn:ss")
05:15:00
?Format(#08/23/2008 8:00:00# - #08/22/2008 5:00 PM#, "hh:nn:ss")
15:00:00

The only time it won't work if if the difference is greater than 24 hours:
you'll lose every multiple of 24.

?Format(#08/23/2008 8:00:00# - #08/01/2008 5:00 PM#, "hh:nn:ss")
15:00:00
 
Douglas J. Steele said:
The only time it won't work if if the difference is greater than 24 hours:
you'll lose every multiple of 24.

?Format(#08/23/2008 8:00:00# - #08/01/2008 5:00 PM#, "hh:nn:ss")
15:00:00
Click to expand...

Aha!!! Mine IS better cuz it will work with intervals greater than 24
hours! Woo Hoo!!! :D

Regards, Chris
 
Back
Top