Date and time

  • Thread starter Thread starter Robert Gillard
  • Start date Start date
R

Robert Gillard

I am using Access 2000

On a form I wish to calculate the length of time an item has ben
outstanding. The start time and end time are detailed as eg 24 Feb 2004
09:13:46.

Ideally I want to know the the number of working days (Mon to Fri, 9 to 5)
an item has been outstanding (ie 4 days 03 hours 16mins) . I do not think it
is as easy as just taking "Start - End" as this does not go past 24 hours.
So do I have to split the time and day into 2 different sections and then
take "startDays - EndDays" and then "startTime - EndTime" and then put them
back together afterwards.

I did find a "DateDiff" but that does not seem to want to work for me,
although it may be because the of the way I have the date is formated to
start with, (General date ie includes time)

Can anyone suggest the best way forward please.

Bob
 
Leaving aside for the moment the question of weekdays vs all days, I think
you would be well served by starting with "End - Start".
Access stores dates as an integer which represents the number of days since
a constant start date (I think it's something like December 31,1899), and
times as fractions of an entire day (e.g. 12:00 noon = .5).
So when you subtract two date/times, you'll get the days (including
fractions) between them. The issue becomes how to present it. You can just
take the integral part of it with the Fix() function to show the number of
days. You can do the hours & minutes using Format(MyDiff,"hh:nn") or some
such.

HTH
- Turtle
 
I am using Access 2000

On a form I wish to calculate the length of time an item has ben
outstanding. The start time and end time are detailed as eg 24 Feb 2004
09:13:46.

Ideally I want to know the the number of working days (Mon to Fri, 9 to 5)
an item has been outstanding (ie 4 days 03 hours 16mins) . I do not think it
is as easy as just taking "Start - End" as this does not go past 24 hours.
So do I have to split the time and day into 2 different sections and then
take "startDays - EndDays" and then "startTime - EndTime" and then put them
back together afterwards.

I did find a "DateDiff" but that does not seem to want to work for me,
although it may be because the of the way I have the date is formated to
start with, (General date ie includes time)
Click to expand...

DateDiff works fine - but it doesn't take holidays, weekends or
evenings off. (Dilbert's boss's favorite employee, DateDiff!)

A Date/Time value is in fact a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899. The Format
is completely irrelevant; all it does is control how (say)
38072.9609837963 is displayed - as 3/26/2004 11:03:49 PM, or as
26-March, or whatever. Your splitting won't help because - again - the
time between 4pm Wednesday and 10am Thursday should be 2 hours by your
complex "duration" algorithm.

You will need some VBA code which can account for evenings, weekends,
*business holidays* (which you didn't mention but should surely be
considered). For a start see the Access Web article on "Workday Math":
http://www.mvps.org/access/datetime/date0012.htm
 
Back
Top