[AccessD] Calculating Date/Time

I would use the DateDiff function


>From the Access 97 Help file.....

----------------------------
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
The firstweekofyear argument has these settings:

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.
----------------------------
On 10/25/06, Hollis, Virginia <hollisvj at pgdp.usec.com> wrote:
> I have a database that tracks elapsed minutes of a project. The elapsed
> minutes are from the date/time a project was opened until the date/time
> it was closed.
>
>
>
> I need to create a query that shows the average time it takes to close a
> project for each technician. The elapsed minutes is a number field.
>
>
>
> What do I need to do to turn all those minutes into something that shows
> how many days, hours, minutes it took to close the project? Then figure
> the average time it takes each tech to close a project.
>
>
>
> What I need:
>
> Average age of a project
>
> Average time it takes to close a project
>
> Something that turns minutes to days (or figure it from dateopened &
> dateCompleted, which is shown as: 12/21/2001 10:07:52 AM
>
>
>
> Virginia
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
Gary Kjos
garykjos at gmail.com