Time Calculation question

[ross182]

I need to be able to figure out how many minutes it takes an employee to perform a set of tasks.

My employees always start thier job at 5AM and complete, say 20 tasks by 7AM, then move to another set of tasks that get completed by say 825AM.

My database already records the number of tasks as a sum. And the time they COMPLETETED the task.

So my reports print out with the employee name, the number of tasks and the time they completed them.

I would like my report to calculate how long it took them to all of thier tasks in total assuming a start time of 5AM and report it in minutes.

So if they started at 5AM and finshed their last task at 8:45, the report would list the times completed and sum the total time as 225 minutes.

What is the code to make this happen?

Then I need to have it tell me how long in minutes it took to complete the total amount of tasks. So in other words if it took 225 minutes to complete 28 tasks I want it to show me that it took 8 minutes per task.

Can anyone help me out with this?

Thanks - I would be grateful.

Ross
Buffalo, NY

[Andrew Thackray]

the function you want is Datediff. This calculates the time between two dates in the units specified. the documentation for the function is

DateDiff Function


Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in the calculation.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.



Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second



The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday



Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.



Remarks

You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.

To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.

The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.

If date1 or date2 is a date literal, the specified year becomes a permanent part of that date. However, if date1 or date2 is enclosed in double quotation marks (" "), and you omit the year, the current year is inserted in your code each time the date1 or date2 expression is evaluated. This makes it possible to write code that can be used in different years.

When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

Note For date1 and date2, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri.

[NeoPa]

Assuming the field is called datFinishTime and you have a variable intMinutes then,

intMinutes = 60 * (Hour(datFinishTime) - 5) + Minute(datFinishTime)

I'm not sure you really want someone to tell you how to divide this by the field with the number of tasks in it ;)

[Killer42]

...
Then I need to have it tell me how long in minutes it took to complete the total amount of tasks. So in other words if it took 225 minutes to complete 28 tasks I want it to show me that it took 8 minutes per task.

Just to expand on a couple of points...

• If you use DateDiff, make sure you noticed that minutes are represented by n, not m. This trap catches a lot of people.
• You want ot be careful of your terminology. In the section highlighted above, what you want to know is the average amount of time to complete each task.