Time Calculations

  • Thread starter Thread starter Bandit
  • Start date Start date
B

Bandit

I need to preform a calculation in a query to add the time for a time card.
I have the following headings:

VolunteerName-Text
TimesheetDate-Date/Time
Assignment-Text
Supervisor-Test
WorkDate-Date/Time
TimeIn-Date/Time
TimeOut-Date/Time
DailyTotalHours-Number
MonthlyTotal-Number

I used an input mask for the timein/timeout to "Short Time" i.e. 13:12.

I am trying to calculate the time to into the DailyTotalHours column. I did
a [TimeOut]-[TimeIn] in the query but didnot get the correct answer. Is
there a better input mask to use for this or did I make an error in the
calculation? I am making up a timecard for volunteers to keep track of their
time. I apppricate any help on this.

Thank you

Bandit
 
I am trying to calculate the time to into the DailyTotalHours column. I did
a [TimeOut]-[TimeIn] in the query but didnot get the correct answer. Is
there a better input mask to use for this or did I make an error in the
calculation? I am making up a timecard for volunteers to keep track of their
time. I apppricate any help on this.
Click to expand...

Use the DateDiff() function instead. A Date/Time value is best used for a
specific point in time, not for a duration; it's stored as a double float
count of days and fractions of a day since midnight, December 30, 1899 - so
subtracting two date/times will probably get a date/time value sometime on
that long-ago day.

DateDiff("n", [TimeIn], [TimeOut])

will get the length of time worked in miNutes ("m" is Months). Divide by 60 to
get hours and fractions of an hour.
 
Thank you for the reply. John, could you be a little more specific. I am
still learning this program and am not familiar with that function. Where do
I find it. Thanks again.
--
Bandit


John W. Vinson said:
I am trying to calculate the time to into the DailyTotalHours column. I did
a [TimeOut]-[TimeIn] in the query but didnot get the correct answer. Is
there a better input mask to use for this or did I make an error in the
calculation? I am making up a timecard for volunteers to keep track of their
time. I apppricate any help on this.
Click to expand...

Use the DateDiff() function instead. A Date/Time value is best used for a
specific point in time, not for a duration; it's stored as a double float
count of days and fractions of a day since midnight, December 30, 1899 - so
subtracting two date/times will probably get a date/time value sometime on
that long-ago day.

DateDiff("n", [TimeIn], [TimeOut])

will get the length of time worked in miNutes ("m" is Months). Divide by 60 to
get hours and fractions of an hour.
Click to expand...
 
Thank you for the reply. John, could you be a little more specific. I am
still learning this program and am not familiar with that function. Where do
I find it. Thanks again.
Click to expand...

You can simply type it in a vacant Field cell. To get help on it, open the VBA
edit window by typing Ctrl-G (in order to get to the VBA help file), press F1,
and search for DateAdd.
 
It really depends, are you trying to do this in a form? Generally it is
considered bad form to store data that can be computed any time you need it
via a query, so I would not normally store either the DailyTotalHours or the
MonthlyTotal values. I would just compute them when I need them.

In a form, I might have a text box (locked so the user could not edit it)
that had a control source that looks something like:

ControlSource: = Round((datediff("n", NZ(me.txtTimeIn, 0), NZ(me.txtTimeOut,
0))/60,2)

This would display the number of hours and fraction thereof in the textbox.
I would probably also apply conditional formatting to default to a Red
background, Yellow if the value in the field is between 1 and 8 hours, Green
if it is between 8 and 10, then some other color if it is over 10 hours. I
do this because I like the color indicator if the value is within a specific
range.

Then, for the hours this month, you could do something like the above with
a new control, only the control source would be something like:

ControlSource: = DSUM(datediff("n", NZ([TimeIn], 0), NZ([TimeOut], 0))/60),
"yourTable",
"EmpID = " & me.txtEmpID & " AND
Format(TimeSheetDate, 'mm/yy') = '" & Format(me.txtDate, "mm/yy") & "'")

If you only want 2 decimal places, set that property in the controls
property window. This might seem a little long, but the DSUM function will
require three parameter in this case, a field or expression to sum, a table
name, and a criteria that will limit the sum to the employee you are working
on, and the month for which they are entering data.

HTH
Dale


Bandit said:
Thank you for the reply. John, could you be a little more specific. I am
still learning this program and am not familiar with that function. Where
do
I find it. Thanks again.
--
Bandit


John W. Vinson said:
I am trying to calculate the time to into the DailyTotalHours column. I
did
a [TimeOut]-[TimeIn] in the query but didnot get the correct answer. Is
there a better input mask to use for this or did I make an error in the
calculation? I am making up a timecard for volunteers to keep track of
their
time. I apppricate any help on this.
Click to expand...

Use the DateDiff() function instead. A Date/Time value is best used for a
specific point in time, not for a duration; it's stored as a double float
count of days and fractions of a day since midnight, December 30, 1899 -
so
subtracting two date/times will probably get a date/time value sometime
on
that long-ago day.

DateDiff("n", [TimeIn], [TimeOut])

will get the length of time worked in miNutes ("m" is Months). Divide by
60 to
get hours and fractions of an hour.
Click to expand...
Click to expand...
 
Back
Top