Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004

    Question Unanswered: How Many Business Days Left

    I need an ACCESS expression / function to determine how many business days are left in a month from any given date going forward. I have used DateDiff but that doesn't exclude weekends. Obviously I am a newbie so any assistance in certainly welcomed.

  2. #2
    Join Date
    Apr 2004
    Sydney Australia

    I don't have a fucntion that will do it but I can get the result.

    Firstly, make a table with one number field and enter 0 to 365 if you want a year.

    Then make a query on the table and add this calculated field. We will call the field that has 0 to 365 NumberDays and the calulated field MyCalendar

    MyCalendar: [NumberDays]+Date()

    This results in the a column that always start with the current date.

    Now add another filed for DayNumber:Weekday([MyCalendar]) which will give 1 for Sunday and 7 for Saturday

    Now add and IIf field Number: IIf([DayNumer] Like 1 Or [DayNumber] Like 7,1,0)

    So we are going to finish with a field that has 1 for Saturday and Sunday and of course 0 for the weekdays.

    Now make the query to select on the above from current date to future date and total the field DayNumber and that of course will count the number of saturdays and Sundays between the two dates

    Ok, now you do date difference in days between the current date and some future date and get a number answer and subtract the result of the Saturday and Sunday count.

    Put an unbound text box on your form and the criteria in the query being
    <=[Forms]![MyForm]![Text box]

    Make a little form on the final count query and have a macro open it and a SetValue macro to run OnCurrent so as to set the count result in a text box on your form.

    Hopefully someone will have a function but if not the above should get you over the line.


  3. #3
    Join Date
    Feb 2004
    Yea you can make a function call(s).

    Do you need to account for a long span of days, such that you may straddle a holiday? Or if for employees vacation days? If so you would need a table of holidays and/or a vacation start/end date tables where you would need to poll if your desired dates span vacation/holidays. Basically, you set up two datediffs one that calculates day of the week for start and end (ie is it on sat/sunday then subtract 2/1 days for the start and vice versa for the end date) then calculate the full weekends between dates taking above info into account. In other words it would be 2*#of weeks +/- days already into week end - holidays (- vacation days).

    The function would do a datediff w/ a "w" for #of weeks and DatePart (w, dateStart/end) for the day of the week 1 = sun, 7 for sat (default), and other determinations discussed above. You can do it, no?

  4. #4
    Join Date
    Apr 2006
    Toronto, On
    This is the same calculation I'm having trouble with ..
    I'm new to Access and VB Code ..
    I'm trying to get this to work on Access 97 and having an extremely difficult time .. I can post the result if I get this to work ..
    I am going to try and factor holidays by adding a numeric value of 8 for all 'holidays' on my calendar table and that way it can be included as a condition in my IF statment ( IF = 1, 7 OR 8 then "0" so that only "weekdays" will be counted ) ..

  5. #5
    Join Date
    Jul 2005
    Provided Answers: 1
    try this

    Sub WeekdayCount(pSDte As Variant, pEDte As Variant)
    'Purpose: Count number of specified weekdays
    ' between two dates
    'Inputs: from debug window:
    ' Call WeekdayCount(#1/11/03#, #11/12/03#)
    'Output: See MsgBox

    Dim StartDte As Date, EndDte As Date, DateHold As Date
    Dim NumDays As Integer, NumWeeks As Integer, FirstDay As Integer
    Dim n As Integer, strHold As String
    Dim NL, TB, fmt, Msg

    NL = Chr(13) & Chr(10) ' Define newline.
    TB = Chr(9) ' Define tab.
    fmt = "dd-mmm-yyyy" 'medium date

    If Not IsDate(pSDte) Or Not IsDate(pEDte) Then Exit Sub
    StartDte = DateValue(pSDte)
    EndDte = DateValue(pEDte)

    'Reverse the dates if they were input backwards
    If StartDte > EndDte Then
    DateHold = StartDte
    StartDte = EndDte
    EndDte = DateHold
    End If
    Msg = "Weekday count for " & NL & "days between " & NL & Format(StartDte, fmt) _
    & " and " & Format(EndDte, fmt) & "@" & NL

    NumWeeks = Int((DateDiff("d", StartDte, EndDte) + 1) / 7)
    NumDays = (DateDiff("d", StartDte, EndDte) + 1) Mod 7
    FirstDay = WeekDay(StartDte)
    strHold = Mid("1234567123456", FirstDay, NumDays)
    For n = 1 To 7
    Msg = Msg & Format(n, "ddd") & ": " & TB _
    & (NumWeeks + IIf(InStr(strHold, n) > 0, 1, 0)) & NL & NL
    Next n

    Msg = Msg & "@" & NL & "Days till XMas (As of " & Date & "): " & DateDiff _
    ("d", Date, DateSerial(Year(Date), 12, 25))

    MsgBox Msg, vbInformation, "Total Days: " _
    & DateDiff("d", StartDte, EndDte) + 1 & " | Total Weeks: " & NumWeeks

    End Sub

  6. #6
    Join Date
    Apr 2006
    Toronto, On
    Wow .. Thanks for the reply ..
    I will try that out, but first it's gonna take me some time to even figure out what that's doing ... lol ..
    That's very advanced stuff, I totally appreciate the help ..

    Thanks ..

Posting Permissions

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