How to work out Hrs

[Anima]

hey there kinda new to access...

how would i work out the amount of hrs worked?
e.g

time start at 8.00am, time finish at 5.00pm

how would i get to say 9hrs?

 

[Anima]

i've try Number of Hours worked: [Time End]-[Time Start]

 

[Dwight]

Take a look at the DateDiff Function.

 

[Jon K]

i've try Number of Hours worked: [Time End]-[Time Start]

[Time End]-[Time Start] returns the figure in Day. So you have to multiply it by 24 to convert it to Hours:-
Number of Hours worked: ([Time End]-[Time Start])*24


If your version of Access has the Round() function and your number of hours may contain decimal places, you can round it to 2 places of decimal like this:-
Number of Hours worked: Round(([Time End]-[Time Start])*24, 2)


However, if some of the times may span across midnight, you will need to add 1 day to the time end when [Time End]<[Time Start] in order to get the correct number of hours:-

Number of Hours worked: Round((IIf([Time End]<[Time Start], [Time End]+1, [Time End])-[Time Start])*24, 2)
.

 

[Anima]

ah thx it works, but one problem

it gives me the hrs in time and not total number e.g

from 10am-3pm = 5hrs by in that formula it gives me 5.00
so how would i format it so it gives me just 5 as a General Number

 

[Jon K]

Since date/time fields are internally stored as double precision numbers, the expression ([Time End]-[Time Start])*24 should return a double precision number whether it is rounded or not.

On my system, 10am to 3pm returns 5 hours both when the expression is rounded and not rounded.

I have attached the database that I used for testing. Maybe you can compare it with yours. The following are the results returned by the query:-

ID	Time Start	Time End		   Hours     Hours Rounded
1	10:00:00 AM	3:00:00 PM		       5	         5
2	10:00:00 AM	3:15:00 PM		    5.25	      5.25
3	10:00:00 AM	3:30:00 PM		     5.5	       5.5
4	10:00:00 AM	3:37:00 PM	5.61666666666667	      5.62

In the query, the Format property of the "Hours" and the "Hours Rounded" fields are left blank, so the number of hours returned in the two columns are displayed as General Numbers. The first record displayed 5 in both columns.
.