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

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

"JOR" wrote:

> 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

On Fri, 20 Jul 2007 15:26:02 -0700, JOR <(E-Mail Removed)> wrote:

>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]

JOR wrote:
> 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 Removed)

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?

"John W. Vinson" wrote:

> On Fri, 20 Jul 2007 15:26:02 -0700, JOR <(E-Mail Removed)> wrote:
>
> >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]
>

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

HtH

Pieter


"Joyce" <(E-Mail Removed)> wrote in message
news:E166249D-01B9-4434-950A-(E-Mail Removed)...
>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?
>
> "John W. Vinson" wrote:
>
>> On Fri, 20 Jul 2007 15:26:02 -0700, JOR <(E-Mail Removed)>
>> wrote:
>>
>> >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]
>>