Diference in hours between to hours

M

Marco

Hello. I'm making a database to manage the employees extra working hours. I
have a column that already have the total of worked by hours by day. I want
to calculate the diference between the worked hours in one day with the
official hours that the employee have to work. Imagine this tree columns:

[Total worded hours] [Hours per day] [Diference]
10:00:00 08:00 02:00

I can't Calculate the Diference. I don't even have the Hours per day, but I
could create it.


Any Idea?
 
J

John Spencer

Is Total work hours a number of hours or minutes or is it a datetime field?
DateTime fields are designed to store a point in time and not a duration of
time. I started work at 10:00:00 (point in time) and worked for 10 hours
(duration).

The dateDiff function will let you calculate the difference (duration)
between two points in time. You would probably want to do that in minutes
since the difference in hours between 09:59:00 and 10:00:00 is one hour -
that is the hour changed from 9 to 10.

DateDiff("n", [Total Hours Worked],[Hours Per Day])

N = minutes, because M = months
 
J

John Vinson

Hello. I'm making a database to manage the employees extra working hours. I
have a column that already have the total of worked by hours by day. I want
to calculate the diference between the worked hours in one day with the
official hours that the employee have to work. Imagine this tree columns:

[Total worded hours] [Hours per day] [Diference]
10:00:00 08:00 02:00

I can't Calculate the Diference. I don't even have the Hours per day, but I
could create it.


Any Idea?
Click to expand...

Access Date/Time fields are mainly designed for storing dates and
times - a specific POINT in time, not a duration. You can use the
DateDiff() function to calculate the number of minutes or hours
between two date/time values, but I'd really suggest that you store
the total worked hours and the hours per day in a Long Integer field
containing the number of minutes worked.

If you have date/time field for StartedWork and LeftWork (say,
containing #07:55 am# and #05:35 pm#) you could use an expression

DateDiff("n", [StartedWork], [LeftWork])

to calculate the 580 minutes elapesed; this could be compared with the
480 minutes per day. You can format the minutes using an expression
like

DateDiff("n", [StartedWork], [LeftWork]) \ 60 & Format(DateDiff("n",
[StartedWork], [LeftWork]) MOD 60, ":00")

to display it as 9:40.


John W. Vinson[MVP]
 
Top