# of Business Days in a Month

raskew · Feb 15, 2006

Hi -

This is just a challenge -- I enjoy them and suspect other folks do too. I've gone brain-dead trying to resolve it and hope that someone can see through the problem and provide an accurate answer.

Problem: Want to determine the number of business days (Mon - Fri) in an input month (mm/yyyy) without resorting to a loop to count the odd days (left after full weeks have been determined). In other words, I'm trying to define a one-liner that will correctly return the number of non-weekend days left over after the full weeks have been determined.

Here's the code up till now. It works in the majority of cases, but bombs about 2 times out of 12. The fatal line is:

Code:

     OddDays = OddDays - Choose(WeekDay(StartDate), 1, 0, 0, 0, 0, 0, 2)

Code:

Function fMonthBusDay(pmmyyyy As String) As Integer
'Input: ? fMonthBusDay("10/2006")
'Output: 22
Dim StartDate As Date
Dim EndDate   As Date
Dim FullWeek  As Integer
Dim OddDays   As Integer

    EndDate = DateAdd("m", 1, DateValue(pmmyyyy)) - 1
    StartDate = DateValue(pmmyyyy)
    FullWeek = Int((EndDate - StartDate + 1) / 7) * 5
    OddDays = (EndDate - StartDate + 1) Mod 7
    'the following line works in most -- but not all -- cases
    OddDays = OddDays - Choose(WeekDay(StartDate), 1, 0, 0, 0, 0, 0, 2)
    
    fMonthBusDay = FullWeek + OddDays

End Function

If you enjoy playing around with a puzzle, I'd sure appreciate your input.

Best wishes,

Bob

DB7 · Feb 15, 2006

This is a loop, but not exactly as you said?

Function AvWorkDays(dteDate As Date) As Integer

Dim dteLastDay As Date, dteFirstDay As Date
Dim intDays As Integer

dteFirstDay = DateSerial(Year(dteDate), Month(dteDate), 1)
dteLastDay = DateSerial(Year(dteDate), Month(dteDate) + 1, 1)

Do Until dteFirstDay = dteLastDay
Select Case Weekday(dteFirstDay)
Case 2 To 6: intDays = intDays + 1
End Select
dteFirstDay = dteFirstDay + 1
Loop

AvWorkDays = intDays

End Function

EMP · Feb 15, 2006

fMonthBusDay:

DateAdd("m",1,DateValue([mm/yyyy]))-1 -DateValue([mm/yyyy])
-(DateDiff("ww", DateValue([mm/yyyy]), DateAdd("m",1,DateValue([mm/yyyy]))-1,7) -(Weekday(DateValue([mm/yyyy]))=7))
-(DateDiff("ww", DateValue([mm/yyyy]), DateAdd("m",1,DateValue([mm/yyyy]))-1,1) -(Weekday(DateValue([mm/yyyy]))=1))
+1

Credit goes to Jon K.

Calculating Number of Work Dates From 2 Date Fields
http://www.access-programmers.co.uk/forums/showthread.php?t=87474

^

raskew · Feb 16, 2006

Many thanks to EMP, DB7 (and Jon K.)

Your responses got me thinking along different lines. The whole idea was to produce a piece of code that didn't rely on loop(s) to arrive at the correct result. Think I screwed up referencing 'one-liner'. Didn't mean just one line of code, but rather a (hopefully one line replacement) for a loop. It turned out to be about three lines. Here's the working solution--have tested it from 01/2005 thru 12/2006.

Code:

Function fMonthBusDay(pmmyyyy As String) As Integer
'Input: ? fMonthBusDay("10/2006")
'Output: 22
Dim StartDate As Date
Dim EndDate   As Date
Dim FullWeek  As Integer
Dim OddDays   As Integer
Dim WeekHold  As String
Dim WeekKeep  As String

    WeekHold = "1234567123456"
    EndDate = DateAdd("m", 1, DateValue(pmmyyyy)) - 1
    StartDate = DateValue(pmmyyyy)
    FullWeek = Int((EndDate - StartDate + 1) / 7) * 5
    OddDays = (EndDate - StartDate + 1) Mod 7
    'string representation of the weekdays contained in OddDays
    WeekKeep = Mid(WeekHold, WeekDay(StartDate), OddDays)
    'added 2 boolean statements here which equate to -1 if True or 0 if False
    OddDays = OddDays + (InStr(WeekKeep, "1") > 0) + (InStr(WeekKeep, "7") > 0)
    
    fMonthBusDay = FullWeek + OddDays

End Function

Is it faster or more efficient than going the looping route?
- Yeah, probably, but not something you're going to notice.

Thanks again for your input!

Bob

# of Business Days in a Month

raskew

AWF VIP

DB7

Registered User.

EMP

Registered User.

raskew

AWF VIP

Similar threads

Users who are viewing this thread