Time difference

J

John

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?
 
J

Jeff Boyce

John

Take a look at the DateDiff() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Graham Mandeno

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")
Click to expand...

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
Click to expand...

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 said:
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?
Click to expand...
 
J

John

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 said:
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")
Click to expand...

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
Click to expand...

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 said:
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?
Click to expand...
Click to expand...
 
J

John Spencer

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
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 said:
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")
Click to expand...
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
Click to expand...
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mandeno

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 said:
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 said:
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")
Click to expand...

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
Click to expand...

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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query based on Difference in time 3
Time Difference > 24 Hours 2
Subtracting time...again 2
querying unrelated tables 7
Date Time Group Math 3
Calculating Meal Period Violations in Access 2
Cycle Time Calculation Returns Blank 1
Calculating the Difference between two times/ dates 4

Top