Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Buffalo, NY
    Posts
    7

    Unhappy Unanswered: Access 2000 How to Auto Sequence Jobs Excluding Weekends / Holidays

    Trying to write a program to auto schedule jobs based on the number of hours it will take to run those jobs excluding weekends / holidays.

    Was able to setup a form to handle auto scheduling of jobs based on user inputing sequence that they would like to run jobs, but I am unable to exclude certain days from the calculation, ie weekends and holidays.

    Setup a weekend / holiday table called tblCalender, but not sure how to incorporate.

    Any input on how I can achieve my goal would be greatly appreciated.

    Thanks.

    See database attached.
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    hah.
    isn't everyone's work calendar different?
    A holiday for me is not always a holiday for you.

    All that being said, as long as everyone uses the same calendar ...

    WHAT THE HECK IS THAT FILE YOU ATTACHED???
    I am not going to open something so mysterious.
    Last edited by The_Duck; 12-12-03 at 12:18.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2003
    Location
    Buffalo, NY
    Posts
    7
    File is just a sample of database.




    Originally posted by The_Duck
    hah.
    isn't everyone's work calendar different?
    A holiday for me is not always a holiday for you.

    All that being said, as long as everyone uses the same calendar ...

    WHAT THE HECK IS THAT FILE YOU ATTACHED???
    I am not going to open something so mysterious.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Sorry but i have Access 97.
    Saludos
    Norberto

  5. #5
    Join Date
    Nov 2003
    Location
    Warren, PA
    Posts
    52
    Originally posted by The_Duck
    hah.
    isn't everyone's work calendar different?
    A holiday for me is not always a holiday for you.

    All that being said, as long as everyone uses the same calendar ...

    WHAT THE HECK IS THAT FILE YOU ATTACHED???
    I am not going to open something so mysterious.
    This will work for the weekends

    Public Function weekdays(StartDate As Date, EndDate As Date, Optional ExcludeDates As String) As Long
    On Error GoTo weekdaysError
    Dim dExclude() As String
    Dim Z As Long
    Dim ZZ As Integer
    Dim Day As String
    Dim DTSkip As Boolean


    dExclude = Split(ExcludeDates, ",")


    For Z = 0 To UBound(dExclude)
    dExclude(Z) = DateValue(dExclude(Z))
    Next Z


    For Z = 0 To DateDiff("d", StartDate, EndDate)

    Day = DatePart("w", DateAdd("d", Z, StartDate), vbSaturday)

    If Day > 2 Then

    For ZZ = 0 To UBound(dExclude)
    If DateAdd("d", Z, StartDate) = dExclude(ZZ) Then

    DTSkip = True
    Exit For
    Else

    DTSkip = False
    End If
    Next ZZ

    If Not DTSkip Then weekdays = weekdays + 1
    End If
    Next Z
    Exit Function

    weekdaysError:
    If Err = 13 Then
    MsgBox Err.Description & vbCr & "Check your dates", vbInformation, "Error " & Err
    Exit Function
    End If
    MsgBox Err.Description, vbInformation, "Error " & Err
    End Function

    Then just call it in a query or form with the dates

    My call from a query
    Newdate: Networkdays([stdate],[enddate])-1

    If I had more time I would change it and try to make it work for you. But if you need any help let me know I may be around later tonight and tomorrow


    Brent

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok. I took a look at your design per your PM ... I don't know what date is to be used for your exclusion ... You might try naming your columns a little bit better (something like "ScheduleDate") ... Off hand what I'd do is use the DatePart function in your query restricting the scheduled dates returned to thos on M - F (using the weekday interval and testing for 2 thru 6) ... Holidays are another problem all together ... THat I'd make a table of Holidays with a simple list of the Day of Year and test the dates against that too by using the DatePart function ...

  7. #7
    Join Date
    Oct 2003
    Location
    Buffalo, NY
    Posts
    7
    Here is what I was talking to you about earlier today, in more detail.

    Say you have the following jobs and you want to schedule them sequentially in order having each days total hours = 24.

    Sequence = Order in which you will run jobs on a particular day.

    Scheduled Estimated Hrs Day
    JOB# Date S equence To Run Total

    22222-01 12/9/03 1 12.00 24.00
    22223-01 12/9/03 2 4.00 24.00
    22224-01 12/9/03 3 8.00 24.00
    22225-01 12/10/03 1 12.00 27.35
    22226-01 12/10/03 2 5.50 27.35
    22227-01 12/10/03 3 9.85 27.35
    22219-01 12/11/03 1 5.00 26.00
    22221-01 12/11/03 2 14.65 26.00
    20597-01 12/11/03 3 3.00 26.00
    22228-01 12/12/03 1 1.00 58.00
    22233-01 12/12/03 2 55.00 58.00
    21111-01 12/16/03 1 14.00 24.00
    22252-02 12/17/03 1 23.00 23.00


    12/9/2003 Total Hours Add Up to 24.00 Hrs.
    12/10/2003 Total Hours Add Up to 27.35 Hrs, but 3.35 hrs to carry over to 12/11.
    12/11/2003 Total Hours Add Up to 26.00 Hrs, 3.35 hrs from 12/10 + 5 + 14.65 + 3, but 2 hrs to carry over to 12/12.
    12/12/2003 Total Hours Add Up to 58.00 Hrs, 2 hrs from 12/11 + 1+ 55, but 24 hrs to carry over to 12/15 & 10 hrs carry over to 12/16.
    12/13/2003 Saturday, not used to schedule.
    12/14/2003 Sunday, not used to schedule.
    12/15/2003 Total Hours Add Up to 24.00 Hrs, 24 hrs carry over from 12/12.
    12/16/2003 Total Hours Add Up to 24.00 Hrs, 10 hrs carry over from 12/12 + 14 scheduled.
    12/17/2003 Total Hours Add Up to 23 Hrs, no other jobs scheduled.


    Now say I need to move up order 22252-02 to be the 2nd job run on 12/12.
    Want schedule to update all scheduled dates and sequences on subsequent jobs automatically using 24 hrs per day.

    Scheduled Estimated Hrs Day
    JOB# Date Sequence To Run Total

    22222-01 12/9/03 1 12.00 24.00
    22223-01 12/9/03 2 4.00 24.00
    22224-01 12/9/03 3 8.00 24.00
    22225-01 12/10/03 1 12.00 27.35
    22226-01 12/10/03 2 5.50 27.35
    22227-01 12/10/03 3 9.85 27.35
    22219-01 12/11/03 1 5.00 26.00
    22221-01 12/11/03 2 14.65 26.00
    20597-01 12/11/03 3 3.00 26.00
    22228-01 12/12/03 1 1.00 26.00
    22252-02 12/12/03 2 23.00 26.00
    22233-01 12/15/03 1 55.00 57.00
    21111-01 12/17/03 1 14.00 23.00


    12/12/2003 Total Hours Add Up to 26.00 Hrs, 2 hrs from 12/11 + 1+ 23, but 2 hrs to carry over to 12/15.
    12/13/2003 Saturday, not used to schedule.
    12/14/2003 Sunday, not used to schedule.
    12/15/2003 Total Hours Add Up to 57.00 Hrs, 2 hrs carry over from 12/12 + 55, but 24 hrs carry over to 12/16, and 9 hrs carry over to 12/17.
    12/16/2003 Total Hours Add Up to 24.00 Hrs, 24 hrs carry over from 12/15.
    12/17/2003 Total Hours Add Up to 23 Hrs, 9 hrs carry over from 12/17 + 14.

    Can you think of a way this would be possible?

    Let me know what you think.

    I appreciate any input.

    Thanks,

    John

Posting Permissions

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