Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access & VBA (2000)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access & VBA (2000)

    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.

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access & VBA (2000)

    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

  4. #4
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access & VBA (2000)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access & VBA (2000)

    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)

  6. #6
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access & VBA (2000)

    Cool! Glad to have use of your knowledge!

    Thanks so much!

    Patty

  7. #7
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access & VBA (2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access & VBA (2000)

    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •