Calculating the Difference between two times/ dates

If I have two date fields with respective time fields, how do I need to
calculate the time difference between two times that sometimes carry over to
the next day? i.e. The time difference between [Time of Onset] which is the
first time at 12/31/2005 18:00 and between [Time of Arrival] which is at
1/01/2006 02:00.
I have previously calculated the difference between two times using:

HMTime: Format([Time of Arrival]-[Time of Onset],"Short Time")

Thanks.

The formula you have should work.

"Simon" wrote:

If I have two date fields with respective time fields, how do I need to
calculate the time difference between two times that sometimes carry over to
the next day? i.e. The time difference between [Time of Onset] which is the
first time at 12/31/2005 18:00 and between [Time of Arrival] which is at
1/01/2006 02:00.
I have previously calculated the difference between two times using:

HMTime: Format([Time of Arrival]-[Time of Onset],"Short Time")

Thanks.

It does work, when the two times are on the same day. But as in the example
below, they are on two separate days. How would I incorporate the date into
the formula for the query?

Thanks.
Simon

"Klatuu" wrote:

The formula you have should work.

"Simon" wrote:

If I have two date fields with respective time fields, how do I need to
calculate the time difference between two times that sometimes carry over to
the next day? i.e. The time difference between [Time of Onset] which is the
first time at 12/31/2005 18:00 and between [Time of Arrival] which is at
1/01/2006 02:00.
I have previously calculated the difference between two times using:

HMTime: Format([Time of Arrival]-[Time of Onset],"Short Time")

Thanks.

What results are you getting. When I use your values:
[Time of Onset] 12/31/2005 18:00
Time of Arrival] 1/01/2006 02:00
And your formula:
Format([Time of Arrival]-[Time of Onset],"Short Time")
I get 8:00 which is correct. I also varied the Onset time to 18:30 and the
results is 7:30, also correct.

I also went to these values:
onset = #12/31/2005 12:59 AM#
arrive = #01/01/2006 01:00 AM#
and got 00:01

Can you tell me what results you are getting to make you believe it is not
working?

"Simon" wrote:

It does work, when the two times are on the same day. But as in the example
below, they are on two separate days. How would I incorporate the date into
the formula for the query?

Thanks.
Simon

"Klatuu" wrote:

The formula you have should work.

"Simon" wrote:

If I have two date fields with respective time fields, how do I need to
calculate the time difference between two times that sometimes carry over to
the next day? i.e. The time difference between [Time of Onset] which is the
first time at 12/31/2005 18:00 and between [Time of Arrival] which is at
1/01/2006 02:00.
I have previously calculated the difference between two times using:

HMTime: Format([Time of Arrival]-[Time of Onset],"Short Time")

Thanks.

One example of skewed data is the following:
[Time of Onset] 5/2/2005 22:00
[Time of Arrival] 5/3/2005 18:13.

Since the times are form two separate days, I get a result of 3:47, when in
actuality the real result should be 20:13. The question I had is how to
incorporate the date values into the aforementioned HMTime formula.

Another example of this issue is:
[Time of Onset] 10/20/2003 8:00
[Time of Arrival] 10/21/2003 4:09
The real result should be 20:09, but I get a result of 3:51. Again it is due
to the date difference.

Thanks.



"Klatuu" wrote:

What results are you getting. When I use your values:
[Time of Onset] 12/31/2005 18:00
Time of Arrival] 1/01/2006 02:00
And your formula:
Format([Time of Arrival]-[Time of Onset],"Short Time")
I get 8:00 which is correct. I also varied the Onset time to 18:30 and the
results is 7:30, also correct.

I also went to these values:
onset = #12/31/2005 12:59 AM#
arrive = #01/01/2006 01:00 AM#
and got 00:01

Can you tell me what results you are getting to make you believe it is not
working?

"Simon" wrote:

It does work, when the two times are on the same day. But as in the example
below, they are on two separate days. How would I incorporate the date into
the formula for the query?

Thanks.
Simon

"Klatuu" wrote:

The formula you have should work.

"Simon" wrote:

If I have two date fields with respective time fields, how do I need to
calculate the time difference between two times that sometimes carry over to
the next day? i.e. The time difference between [Time of Onset] which is the
first time at 12/31/2005 18:00 and between [Time of Arrival] which is at
1/01/2006 02:00.
I have previously calculated the difference between two times using:

HMTime: Format([Time of Arrival]-[Time of Onset],"Short Time")

Thanks.