Determine Time Difference Between Records

[Guest]

I am trying to calculate the time differnce between two records. For
example, I need to determine how much time was spent working on a task (see
Type below). If LTO #1 started work at 7:00 AM and then completed his 1st
transaction at 8:30AM, I need the time difference to be :30. There is only
one record of time for each event (see Max time below). So for Type 800
below, we need the difference between 7:33:18 and the previous event
7:24:02... See table below with a column added for the desired results (Time
Diff):

LTO Date Type Max Time Time Diff
AMC8863 9/8/2004 901 7:24:02 AM :24:02
AMC8863 9/8/2004 800 7:33:18 AM :09:16
AMC8863 9/8/2004 901 7:40:52 AM :07:34
AMC8863 9/8/2004 901 7:42:52 AM
AMC8863 9/8/2004 101 7:48:00 AM
AMC8863 9/8/2004 901 7:52:36 AM
AMC8863 9/8/2004 901 7:55:39 AM
AMC8863 9/8/2004 901 8:04:48 AM

Thanks,

Nick

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/?id=210504

It would be easier if you combine the date and time into a single field, but
you could use:
CDate([Date] + [Time])

Use DateDiff("n", ..., ...) to get the difference in minutes (or "s" for
seconds if you need that). Best to work in a fixed whole number like that,
and format the result however you want at the end, e.g.:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

 

[Guest]

It worked... Thanks!

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/?id=210504

It would be easier if you combine the date and time into a single field, but
you could use:
CDate([Date] + [Time])

Use DateDiff("n", ..., ...) to get the difference in minutes (or "s" for
seconds if you need that). Best to work in a fixed whole number like that,
and format the result however you want at the end, e.g.:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to calculate the time differnce between two records. For
example, I need to determine how much time was spent working on a task
(see
Type below). If LTO #1 started work at 7:00 AM and then completed his 1st
transaction at 8:30AM, I need the time difference to be :30. There is
only
one record of time for each event (see Max time below). So for Type 800
below, we need the difference between 7:33:18 and the previous event
7:24:02... See table below with a column added for the desired results
(Time
Diff):

LTO Date Type Max Time Time Diff
AMC8863 9/8/2004 901 7:24:02 AM :24:02
AMC8863 9/8/2004 800 7:33:18 AM :09:16
AMC8863 9/8/2004 901 7:40:52 AM :07:34
AMC8863 9/8/2004 901 7:42:52 AM
AMC8863 9/8/2004 101 7:48:00 AM
AMC8863 9/8/2004 901 7:52:36 AM
AMC8863 9/8/2004 901 7:55:39 AM
AMC8863 9/8/2004 901 8:04:48 AM

Thanks,

Nick