add or subtract time difference

[Guest]

hello,

just want to get an answer from experts. i have database that needs to
calculate the time difference in 24 hour time format: see example.

onblock = 14:00
offblock = 13:20
on/off block diff = 00:40

im calculating from query and onblock, offblock are in "short time"format.

Please help. ..

Thanks,

JOR

 

[Guest]

Check the Access help file for info on the DateDiff function. You can use it
to tell the difference between two Date/Time fields. It can calculate by
seconds, minutes, hours, days, etc.

HTH

 

[John W. Vinson]

hello,

just want to get an answer from experts. i have database that needs to
calculate the time difference in 24 hour time format: see example.

onblock = 14:00
offblock = 13:20
on/off block diff = 00:40

im calculating from query and onblock, offblock are in "short time"format.

The format is completely irrelevant: a Date/Time value is stored as a number,
a count of days and fractions of a day since midnight, December 30, 1899.

I'd use DateDiff to calculate the duration in minutes, and then cast the
result using an expression:

DateDiff("n", [offblock], [onblock]) \ 60 & ":" & Format(DateDiff("n",
[offblock], [onblock]) MOD 60, "00")

John W. Vinson [MVP]

 

[James A. Fortune]

hello,

just want to get an answer from experts. i have database that needs to
calculate the time difference in 24 hour time format: see example.

onblock = 14:00
offblock = 13:20
on/off block diff = 00:40

im calculating from query and onblock, offblock are in "short time"format.

Please help. ..

Thanks,

JOR

If you want maximum flexibility in calculating time differences, don't
store just the hour and minute. By including the date along with the
hour and minute you can calculate time differences that go past midnight
and that span multiple days. Once you calculate the duration in minutes
using DateDiff you can still use John's expression or modify it to
handle days also.

James A. Fortune
(e-mail address removed)

 

[Guest]

I have a further question in regards to your answer here. The formula helped
me calculate the time and I am now trying to run another query calculating
the difference time and the standard time that the job should take. The
standard time was set up over a year ago as a "number". I now get errors
when trying to calculate the diff between the standard time and the actual
time due to the format. What is the easiest way to correct this?

hello,

just want to get an answer from experts. i have database that needs to
calculate the time difference in 24 hour time format: see example.

onblock = 14:00
offblock = 13:20
on/off block diff = 00:40

im calculating from query and onblock, offblock are in "short time"format.

The format is completely irrelevant: a Date/Time value is stored as a number,
a count of days and fractions of a day since midnight, December 30, 1899.

I'd use DateDiff to calculate the duration in minutes, and then cast the
result using an expression:

DateDiff("n", [offblock], [onblock]) \ 60 & ":" & Format(DateDiff("n",
[offblock], [onblock]) MOD 60, "00")

John W. Vinson [MVP]

 

[Pieter Wijnen]

If the number is "hours" Divide by 24 (1hr, 1.5 hrs etc)

HtH

Pieter

I have a further question in regards to your answer here. The formula
helped
me calculate the time and I am now trying to run another query calculating
the difference time and the standard time that the job should take. The
standard time was set up over a year ago as a "number". I now get errors
when trying to calculate the diff between the standard time and the actual
time due to the format. What is the easiest way to correct this?

hello,

just want to get an answer from experts. i have database that needs to
calculate the time difference in 24 hour time format: see example.

onblock = 14:00
offblock = 13:20
on/off block diff = 00:40

im calculating from query and onblock, offblock are in "short
time"format.

The format is completely irrelevant: a Date/Time value is stored as a
number,
a count of days and fractions of a day since midnight, December 30, 1899.

I'd use DateDiff to calculate the duration in minutes, and then cast the
result using an expression:

DateDiff("n", [offblock], [onblock]) \ 60 & ":" & Format(DateDiff("n",
[offblock], [onblock]) MOD 60, "00")

John W. Vinson [MVP]