If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating Working Hours that cross over midnight
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? |
#2
|
|||
|
|||
Calculating Working Hours that cross over midnight
On Tue, 1 Jul 2008 15:35:01 -0700, Jurrasicway wrote:
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 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#3
|
|||
|
|||
Calculating Working Hours that cross over midnight
If you include the date with the time it will work correctly.
Or this --- WorkingTime: IIF([FinishTime]-[StartTime]0,[FinishTime]+1-[StartTime],[FinishTime]-[StartTime]) -- KARL DEWEY Build a little - Test a little "Jurrasicway" wrote: 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? |
#4
|
|||
|
|||
Calculating Working Hours that cross over midnight
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" wrote in message ... 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? |
#5
|
|||
|
|||
Calculating Working Hours that cross over midnight
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. "Jeff Boyce" wrote: 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" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|