Calculating the differance between 2 time fields

J

JWG

I have 2 time fields in a query that i want to know the differance of. I want
the result to formatted "hh,mm" in the field. Can this happen in a query?
 
D

Douglas J. Steele

You can use

DateDiff("n", [Time1], [Time2])\60 & ":" & Format(DateDiff("n", [Time1],
[Time2]) Mod 60, "00")

Note that this returns a string, so you will not be able to do arithmetic on
the result.
 
J

JWG

Ok that will help, but what happens if the time in is like 23:00 and the time
out is 01:00 the next morning. I get -23:00 as the answer not 2 which is the
true time on the job.

Douglas J. Steele said:
You can use

DateDiff("n", [Time1], [Time2])\60 & ":" & Format(DateDiff("n", [Time1],
[Time2]) Mod 60, "00")

Note that this returns a string, so you will not be able to do arithmetic on
the result.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JWG said:
I have 2 time fields in a query that i want to know the differance of. I
want
the result to formatted "hh,mm" in the field. Can this happen in a query?
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Realistically, your fields should contain both Date and Time. Access doesn't
really support time-only. (The Date data type is an 8 byte floating point
number, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day. In other words, if you're only storing 23:00 and 1:00,
you're actually storing 23:00 on 30 Dec, 1899 and 1:00 on 30 Dec, 1899)

If it's too late to go back and do it correctly, see
http://www.mvps.org/access/datetime/date0008.htm at "The Access Web".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JWG said:
Ok that will help, but what happens if the time in is like 23:00 and the
time
out is 01:00 the next morning. I get -23:00 as the answer not 2 which is
the
true time on the job.

Douglas J. Steele said:
You can use

DateDiff("n", [Time1], [Time2])\60 & ":" & Format(DateDiff("n", [Time1],
[Time2]) Mod 60, "00")

Note that this returns a string, so you will not be able to do arithmetic
on
the result.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JWG said:
I have 2 time fields in a query that i want to know the differance of. I
want
the result to formatted "hh,mm" in the field. Can this happen in a
query?
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Ok that will help, but what happens if the time in is like 23:00 and the time
out is 01:00 the next morning. I get -23:00 as the answer not 2 which is the
true time on the job.
Click to expand...

I absolutely agree with Douglas that you should store the date and time. I
doubt it would arise in a workshift situation, but you could span TWO
midnights - from 23:00 to 1:00 might be two hours or it might be 26, and if
the time is all you're storing there's no way to tell.

That said... you can get the positive time if you assume that the total span
will never exceed 24 hours:

(DateDiff("n", [start], [end])+IIF([start] > [end], 1440, 0) \ 60 &
Format(DateDiff("n", [start], [end]) MOD 60, ":00")
 
Top