Re: Time difference
- From: John Spencer <spencer@xxxxxxxxx>
- Date: Tue, 11 Nov 2008 07:26:52 -0500
DateTime fields contains a point in time. So, a time can never be later than 23:59:59 and it can never be earlier than 00:00:00. Also, there is no such time (point in time) as negative one o'clock.
What you are calculating is a DURATION of time - and they only way that can be expressed is in one unit of time (seconds, minutes, hours, etc.).
The string that Graham has constructed breaks a duration measered in secondes into a human readable form.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John wrote:
Thanks for your help.....this works but it requires the Late field to be a text field.......is there a way to get it so the late field can be date/time format?.
"Graham Mandeno" wrote:
Hi John
Late: Format(DateAdd("h",-4,Format([FLT_ACT_ARR_TM],"ShortThis is a very strange expression you are using. For a start, why are you subtracting 4 hours from each of the times before you calculate the difference between them? Also, you are converting each of the times to a string, using the Format function, before you do any manipulation. At best this will be inefficient and at worst will give you garbage results.
Time"))-DateAdd("h",-4,Format([FLT_SCH_ARR_TM],"Short Time")),"Short Time")
The best way to calculate the difference between two times is with the DateDiff function. This will give you an integer number of the units you specify, either positive or negative.
For example:[FLT_SCH_ARR_TM]= 5:46:00 AMDateDiff("s", [FLT_SCH_ARR_TM], [FLT_ACT_ARR_TM])
[FLT_ACT_ARR_TM]= 5:36:00 AM
will return -600, indicating the actual time was 600 seconds earlier than the scheduled time.
All you need now is a function to format a number of seconds into whatever string format you require.
Say you want hh:mm:ss.
Public Function FormatSeconds(vSeconds As Variant) As String
Dim lSeconds As Long
Dim h As Long, m As Integer, s As Integer
Dim sSign As String
If IsNumeric(vSeconds) Then
lSeconds = vSeconds
If vSeconds < 0 Then
sSign = "-"
lSeconds = -lSeconds
Else
sSign = "+"
End If
h = lSeconds \ 3600
m = (lSeconds \ 60) Mod 60
s = lSeconds Mod 60
FormatSeconds = sSign & Format(h, "00") _
& Format(m, "\:00") & Format(s, "\:00")
End If
End Function
You can pass your DateDiff expression directly to the function if you wish:
Late: FormatSeconds(DateDiff("s", [FLT_SCH_ARR_TM], [FLT_ACT_ARR_TM]))
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"John" <John@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:46403BAB-A9B6-460F-ACD6-6C2A09E6C9BF@xxxxxxxxxxxxxxxxI have two fields [FLT_SCH_ARR_TM] & [FLT_ACT_ARR_TM] both in date/time
format. I am trying to find the difference between.
I am using this formula
Late: Format(DateAdd("h",-4,Format([FLT_ACT_ARR_TM],"Short
Time"))-DateAdd("h",-4,Format([FLT_SCH_ARR_TM],"Short Time")),"Short Time")
The only thing is that it doesn't return a "-" if it's supposed to...they
are all positive times.
Example:
[FLT_SCH_ARR_TM]= 5:46:00 AM
[FLT_ACT_ARR_TM]= 5:36:00 AM
Late returns 00:10 when it should be -00:10
Any suggestions?
- References:
- Time difference
- From: John
- Re: Time difference
- From: Graham Mandeno
- Re: Time difference
- From: John
- Time difference
- Prev by Date: Re: Monthly report using criteria
- Next by Date: Re: query requires value for a field in order to display results
- Previous by thread: Re: Time difference
- Next by thread: Re: Time difference
- Index(es):
Relevant Pages
|
