Calculating time difference problem

[Neil Grantham]

Hi,

I have a table, with times stored for comptetors in races,
With the kind help of Allen Brown, I am now successfully storing in a
query the time for the competitor, and the previous competitors time,
with the aim of showing a time difference

Time are stored thus
05:32:50 (5 hours, 32 minutes and 50 seconds)

I'd like to return in a separate column, the time current competitor
was behind the previous
So, I used
Diff: DateDiff("n",[Priortime],[time])/60

However with values for Priortime and time as 05:42:04 and 05:42:30 I
get the result 0 (should be 26 seconds!) and for
05:42:04 and 05:43:39 gives the answer 0166666666666667 (should be 1
minute 35 seconds)
If I reverse the two fields in the equation I just get the same as
above with '-' (negative) values

What am I doing wrong?

I'd really like to get an answer formatted 00:00:26

Thanks for any help
Neil

 

[John Vinson]

Hi,

I have a table, with times stored for comptetors in races,
With the kind help of Allen Brown, I am now successfully storing in a
query the time for the competitor, and the previous competitors time,
with the aim of showing a time difference

Time are stored thus
05:32:50 (5 hours, 32 minutes and 50 seconds)

I'd like to return in a separate column, the time current competitor
was behind the previous
So, I used
Diff: DateDiff("n",[Priortime],[time])/60

However with values for Priortime and time as 05:42:04 and 05:42:30 I
get the result 0 (should be 26 seconds!) and for
05:42:04 and 05:43:39 gives the answer 0166666666666667 (should be 1
minute 35 seconds)
If I reverse the two fields in the equation I just get the same as
above with '-' (negative) values

What am I doing wrong?

DateDiff returns a value in integer seconds, minutes, years or
whatever argument you pass. If you use the "n" argument, you'll get
integer minutes: 0:0:00 to 0:0:29 will give 0, to 0:0:30 will give 1.

To get seconds, and display them as hours:minutes:seconds, use

DateDiff("s", [PriorTime], [Time]) \ 3600 & ":" & Format(DateDiff("s",
[PriorTime], [Time]) \ 60 MOD 60, "00") & ":" & Format(DateDiff("s",
[PriorTime], [Time]) MOD 60, "00")

Thus if Charmus is behind by 3664 seconds, you'll see 1:01:04.

John W. Vinson[MVP]

 

[John Spencer]

Quibble,

DateDiff has always just given me boundaries crossed. So minutes between
00:00:00 to 00:00:59 returns 0 not 1.

Hi,

I have a table, with times stored for comptetors in races,
With the kind help of Allen Brown, I am now successfully storing in a
query the time for the competitor, and the previous competitors time,
with the aim of showing a time difference

Time are stored thus
05:32:50 (5 hours, 32 minutes and 50 seconds)

I'd like to return in a separate column, the time current competitor
was behind the previous
So, I used
Diff: DateDiff("n",[Priortime],[time])/60

However with values for Priortime and time as 05:42:04 and 05:42:30 I
get the result 0 (should be 26 seconds!) and for
05:42:04 and 05:43:39 gives the answer 0166666666666667 (should be 1
minute 35 seconds)
If I reverse the two fields in the equation I just get the same as
above with '-' (negative) values

What am I doing wrong?

DateDiff returns a value in integer seconds, minutes, years or
whatever argument you pass. If you use the "n" argument, you'll get
integer minutes: 0:0:00 to 0:0:29 will give 0, to 0:0:30 will give 1.

To get seconds, and display them as hours:minutes:seconds, use

DateDiff("s", [PriorTime], [Time]) \ 3600 & ":" & Format(DateDiff("s",
[PriorTime], [Time]) \ 60 MOD 60, "00") & ":" & Format(DateDiff("s",
[PriorTime], [Time]) MOD 60, "00")

Thus if Charmus is behind by 3664 seconds, you'll see 1:01:04.

John W. Vinson[MVP]

 

[John Vinson]

Quibble,

DateDiff has always just given me boundaries crossed. So minutes between
00:00:00 to 00:00:59 returns 0 not 1.

Thanks John - right you are of course!

John W. Vinson[MVP]

 

[Neil Grantham]

Thanks John, just what I needed.
Of course the first will be need to be blank, and at the moment just
returns :: so can I build in something to make it 00:00:00?

Thanks

Neil

Hi,

I have a table, with times stored for comptetors in races,
With the kind help of Allen Brown, I am now successfully storing in a
query the time for the competitor, and the previous competitors time,
with the aim of showing a time difference

Time are stored thus
05:32:50 (5 hours, 32 minutes and 50 seconds)

I'd like to return in a separate column, the time current competitor
was behind the previous
So, I used
Diff: DateDiff("n",[Priortime],[time])/60

However with values for Priortime and time as 05:42:04 and 05:42:30 I
get the result 0 (should be 26 seconds!) and for
05:42:04 and 05:43:39 gives the answer 0166666666666667 (should be 1
minute 35 seconds)
If I reverse the two fields in the equation I just get the same as
above with '-' (negative) values

What am I doing wrong?

DateDiff returns a value in integer seconds, minutes, years or
whatever argument you pass. If you use the "n" argument, you'll get
integer minutes: 0:0:00 to 0:0:29 will give 0, to 0:0:30 will give 1.

To get seconds, and display them as hours:minutes:seconds, use

DateDiff("s", [PriorTime], [Time]) \ 3600 & ":" & Format(DateDiff("s",
[PriorTime], [Time]) \ 60 MOD 60, "00") & ":" & Format(DateDiff("s",
[PriorTime], [Time]) MOD 60, "00")

Thus if Charmus is behind by 3664 seconds, you'll see 1:01:04.

John W. Vinson[MVP]

 

[John Vinson]

Thanks John, just what I needed.
Of course the first will be need to be blank, and at the moment just
returns :: so can I build in something to make it 00:00:00?

Use

NZ(DateDiff(...)) in place of just plain DateDiff.

John W. Vinson[MVP]

 

[Neil Grantham]

Thanks once again John,

This forum has proved most helpful.

For the record, I modified the statement slightly to this:
Diff: Format(nz(DateDiff("s",[PriorTime],[Time])\3600,0) Mod 60,"+00")
& ":" & Format(nz(DateDiff("s",[PriorTime],[Time])\60,0) Mod 60,"00") &
":" & Format(nz(DateDiff("s",[PriorTime],[Time]),0) Mod 60,"00")

as I wanted 2 leading zero's and a + sign

Thanks again
Neil