Re: Time difference
- From: "Graham Mandeno" <Graham.Mandeno@xxxxxxxxxxxxx>
- Date: Wed, 12 Nov 2008 08:46:36 +1300
Hi John
As John S has pointed out, a date/time data type is entirely inappropriate
for storing a time *duration*. It is intended only for a *point* in time.
The fact that you can perform arithmetic on date/time values is misleading
and irrelevant.
If you need to perform calculations on the Late calculation, then leave it
as seconds:
Late: DateDiff("s", [FLT_SCH_ARR_TM], [FLT_ACT_ARR_TM])
You can then sum them or average them or do whatever else you need, and
*then* use the FormatSeconds function to display the result as a more
user-friendly string.
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"John" <John@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A24DBD30-CC10-4D37-AB7C-1E8BCCCBAC6D@xxxxxxxxxxxxxxxx
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],"Short
Time"))-DateAdd("h",-4,Format([FLT_SCH_ARR_TM],"Short Time")),"Short
Time")
This 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.
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 AM
[FLT_ACT_ARR_TM]= 5:36:00 AM
DateDiff("s", [FLT_SCH_ARR_TM], [FLT_ACT_ARR_TM])
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@xxxxxxxxxxxxxxxx
I 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: DateDiff Select Statement
- Next by Date: Re: Calculate business days
- Previous by thread: Re: Time difference
- Next by thread: Date calculation using Iff statement in update query????
- Index(es):
Relevant Pages
|
