Access & VBA (2000)

[paustin]

I have been searching through posts for the past hour trying to find information on doing the following and can't quite find what I need:
I have Date In, Time In, Date Out and Time Out fields in a table
I need to calculate the number of hours it took to process between date/time in and date/time out
I have VBA code working to calculate number of days and not include weekends and holidays.
When calculating the time difference and only include between 8am and 5pm

I'm having trouble figuring out how to calc the time if it goes over multiple days

Thought if anyone had already done this I wouldn't try to reinvent the wheel!

Thanks!

[HansV]

If TimeOut is greater than or equal to TimeIn, calculate the number of days (taking weekends and holidays into account), and multiply by the number of hours per day (9 hours between 8 am and 5 pm). Add the (positive) difference between TimeOut and TimeIn,

If TimeOut is less than TimeIn, subtract 1 from the number of days between DateIn and DateOut, and add 9 (the number of hours in a working day) to TimeOut before subtracting TimeIn.

For example:
DateIn/TimeIn = Wed April 9, 4 PM (=16:00)
DateOut/TimeOut = Mon April 14, 9 AM (=9:00)
The difference between April 9 and April 14 is 3 days (14-9 = 5, subtracting 2 weekend days results in 3); since TimeOut is less than TimeIn, subtract 1, leaving 2 days = 18 hours. Add 9 to TimeOut yielding 9+9 = 18; subtract TimeIn leaving 18-16 = 2. Total 18+2 = 20 hours.

[paustin]

Thanks - I have most of it working the way you described. I'm having trouble with the crossover to the next day and am not seeing what I have wrong:

EX: Date In/Time = 1/8/03 11:02 am
Date Out/Time = 1/9/03 3:30 pm

As a result of "NumWorkHours = (numworkdays * 9) + Abs(EndTime - BeginTime)" I get 9.186111 instead of 13...

Can you tell from this what I have wrong?

Also, I'm going to make it use 5:00 pm if the end time is blank - but I don't know how to check a time field for null

Patty

[paustin]

Hans,

I took the result from the example above *24 and it gives me the right number (I saw that in another post somewhere).

I still don't know how to do the null time though....

Thanks so much,

Patty

[HansV]

Sorry, my bad. Time values are stored internally as fractions of one day, so you must multiply the time difference by 24 to get hours.

Also, Access seems to be happy to calculate negative time differences, so there is no need to juggle things by subtracting here and adding there. Note: don't use Abs here!

Finally, you can use the Nz function to replace a Null value by something else. So you can use

NumWorkHours = NumWorkDays * 9 + 24 * (Nz(EndTime, #5 PM#) - BeginTime)

[paustin]

Cool! Glad to have use of your knowledge!

Thanks so much!

Patty

[paustin]

Not completely right yet ....

When I have an EndTime < BeginTime

EX: Begin Date/Time = 1/15/03 and 2:57 pm
End Date/Time = 1/22/03 and 8:30 am (includes 2 weekend day and 1 holiday)
When I use: NumWorkHours = (((numworkdays - 1) * 9) + ((EndTime + 9) - BeginTime))
I get: 35.17325 and I think it should be around 29 .5
when I use the step process in the VB code it shows: (EndTime + 9) = 1/8/1900 8:30am
and: ((EndTime + 9) - Begin Time) = 8.73
Can you tell what I am missing?

Thanks

[HansV]

You left out the multiplication by 24 again. Furthermore, as I wrote in my previous reply, you don't need to subract 1 from the days and add 9 to the hours. Use 9 * NumWorkdays + 24 * (EndTime - BeginTime).