Calculating Working Hours that cross over midnight

[Jurrasicway]

Hi,
I am trying to make a query work that will calculate hours worked. However,
when my working time spans midnight I get negitive figures. My expression to
calculate the expression in the Field Line of the query is:
workingTime: [FinishTime]-[StartTime] The result is expressed in days
To convert to hours my expression is:
workingHours: [workingTime]*24

When the [StartTime] is 20:45 and the [FinishTime] is 01:30 I return -19.25
I need to return + 4.75
Can anyone help please?

 

[fredg]

Hi,
I am trying to make a query work that will calculate hours worked. However,
when my working time spans midnight I get negitive figures. My expression to
calculate the expression in the Field Line of the query is:
workingTime: [FinishTime]-[StartTime] The result is expressed in days
To convert to hours my expression is:
workingHours: [workingTime]*24

When the [StartTime] is 20:45 and the [FinishTime] is 01:30 I return -19.25
I need to return + 4.75
Can anyone help please?

You need to include the date as well as the time.

Using [Finish] - [Start] * 24:

?(#6/30/2008 01:30# - #6/29/2008 20:45# )*24
4.74999999994179

Using the DateDiff function to return minutes and divide by 60.
Note the Start is entered before the Finish:

?DateDiff("n",#6/29/2008 20:45# ,#6/30/2008 01:30#)/60
4.75

Returning the data in hours and minutes format:

?Int(DateDiff("n",#6/29/2008 20:45# ,#6/30/2008 01:30#)/60) & " hrs
and " & DateDiff("n",#6/29/2008 20:45# ,#6/30/2008 01:30#) Mod 60 & "
minutes"
4 hours and 45 minutes

 

[KARL DEWEY]

If you include the date with the time it will work correctly.

Or this ---
WorkingTime:
IIF([FinishTime]-[StartTime]<0,[FinishTime]+1-[StartTime],[FinishTime]-[StartTime])

 

[Jeff Boyce]

It all starts with the data...

What data type are you storing in [FinishTime] and [StartTime]?

If you store a date/time value, subtracting a [StartDateTime] yesterday from
a [FinishDateTime] today will work.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jurrasicway]

Thanks Jeff and Fred but I think Karls is the way to go. The record set
starts with Location, visit date, start time, finish time. The date is not
stored in the starttime and finishtime fields. I will test and let you know
how I get on. Thanks for your help. If you can think of anything else let me
know
Graeme.

It all starts with the data...

What data type are you storing in [FinishTime] and [StartTime]?

If you store a date/time value, subtracting a [StartDateTime] yesterday from
a [FinishDateTime] today will work.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I am trying to make a query work that will calculate hours worked.
However,
when my working time spans midnight I get negitive figures. My expression
to
calculate the expression in the Field Line of the query is:
workingTime: [FinishTime]-[StartTime] The result is expressed in
days
To convert to hours my expression is:
workingHours: [workingTime]*24

When the [StartTime] is 20:45 and the [FinishTime] is 01:30 I
return -19.25
I need to return + 4.75
Can anyone help please?