Working Days in a Month NOT between two dates

[andy_dyer]

All,

I need to calculate the number of working days excluding weekends and bank holidays by month my current query is this:

SELECT qrySeniorTeamBillable.Year, qrySeniorTeamBillable.Month, qrySeniorTeamBillable.[Total Billable Hours], MyCountWorkDays([Month]) AS [Working Days]
FROM qrySeniorTeamBillable;

Based on the following function:

Public Function MyCountWorkDays(dDate As Date) As Integer
    Dim FirstDayOfMonth As Date
    Dim LastDayOfMonth As Date
    
    FirstDayOfMonth = DateSerial(Year(dDate), Month(dDate), 1)
    LastDayOfMonth = DateSerial(Year(dDate), Month(dDate) + 1, 0)
    
    ' Total number of days in a month minus Saturdays and Sundays:-
    MyCountWorkDays = (LastDayOfMonth - FirstDayOfMonth + 1) _
         - (DateDiff("ww", FirstDayOfMonth, LastDayOfMonth, vbSaturday) _
            - (Weekday(FirstDayOfMonth) = vbSaturday)) _
         - (DateDiff("ww", FirstDayOfMonth, LastDayOfMonth, vbSunday) _
            - (Weekday(FirstDayOfMonth) = vbSunday))
    
End Function

Any idea why i get #Error in my Working Days field??

 

[namliam]

Because you are sending a MONTH to the function where you are supposed to send a DATE

Try changing: MyCountWorkDays([Month]) AS [Working Days]
To: MyCountWorkDays(dateserial ([Year], [Month],1) ) AS [Working Days]


Good luck!

 

[andy_dyer]

Because you are sending a MONTH to the function where you are supposed to send a DATE

Try changing: MyCountWorkDays([Month]) AS [Working Days]
To: MyCountWorkDays(dateserial ([Year], [Month],1) ) AS [Working Days]


Good luck!

Thanks namliam,

I get an invalid syntax error and it doesn't like the AS statement... ???

 

[namliam]

MyCountWorkDays(dateserial ([Year], [Month],1) )

That should be valid... just try that...

 

[Brianwarnock]

I couldn't see anything wrong, so I created a little table and tried it even using Year and Month as field names, something I would never do, but it worked, 21 working days in May 2009 22 in Jan.

Brian

 

[andy_dyer]

MyCountWorkDays(dateserial ([Year], [Month],1) )

That should be valid... just try that...

That gets rid of the syntax error message but I still have #Error in every line of my query...

SELECT qrySeniorTeamBillable.Year, qrySeniorTeamBillable.Month, qrySeniorTeamBillable.[Total Billable Hours], MyCountWorkDays(DateSerial([Year],[Month],1)) AS [Working Days]
FROM qrySeniorTeamBillable;

What am I missing??

 

[Brianwarnock]

We have assumed that month is numeric, and not Jan Feb etc, that is so isn't it?

Brian

 

[andy_dyer]

We have assumed that month is numeric, and not Jan Feb etc, that is so isn't it?

Brian

Nope...

Month is a calculated in the previous query using the following query:

SELECT Year([Entry Date]) AS [Year], Format([Entry Date],"mmm") AS [Month], Sum(qryData.[Billable Hrs]) AS [Total Billable Hours]
FROM qryData
GROUP BY Year([Entry Date]), Format([Entry Date],"mmm");

I am simply trying to get monthly totals for hours when all I have at present is entry dates... I found an example on this forum for this hence the above...

Any improvements are welcomed but at present the month is a three character field such as Jan, Feb, Mar etc

 

[andy_dyer]

I've just changed the "mmm" to "mm" and it gave me numbers!

This then feeds through and works!

Is there a way to convert the numbers 01, 02 etc back to Jan, Feb for reporting purposes?

Plus also am i right that my roiginal code only excludes weekends... can it also exclude bank holidays without a holiday table being required?

Elsewhere in my database I use soem great code to achieve this which I found on this forum but this requires a start and end date and for this purpose I just want calendar months...

Public dtmDateLower As Date
Public dtmDateUpper As Date
Dim LngDepartment As Long

Public Function GetDateLower() As Date
    'This function retains the lower date passed to the public variable via a form
    GetDateLower = dtmDateLower
End Function

Public Function GetDateUpper() As Date
    'This function retains the upper date passed to the public variable via a form
    GetDateUpper = dtmDateUpper
End Function

Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date, _
YCnt As Boolean, Optional pExcl As String = "17", Optional dtmEmpStart As Date, Optional dtmEmpEnd As Date) As Integer
'*****************************************************
'Name:      CalcWkDays2 (Function)
'Purpose:   Count # of days between two dates, with
'           options to:
'           (1) Include or exclude the start date in count
'           (2) Specify weekdays to exclude (default
'               set to Saturday (7) & Sunday (1).  To exclude
'               Tuesday (3) & Thursday (5), specify "35".
'Parameters:    dteStartDate & dteEndDate formatted as dates
'               YCnt:   Specify True to include start date in
'                       count, False to exclude it.
'               pExcl:  Weekdays to exclude
'Inputs:    From debug window:
'           (1) ? CalcWkDays2(#01/01/01#, #07/01/01#, True)
'           (2) ? CalcWkDays2(#01/01/01#, #07/01/01#, False)
'           (3) ? CalcWkDays2(#01/01/01#, #07/01/01#, True, "")
'           (4) ? CalcWkDays2(#01/01/01#, #07/01/01#, False,"")
'           (5) ? CalcWkDays2(#07/01/01#, #01/01/01#, False,"")
'           (6) ? CalcWkDays2(#01/01/01#, #07/01/01#, False,"23456")
'Output:    (1) 130; (2) 129; (3) 182; (4) 181; (5) -181; (6) 52
'
'*****************************************************

Dim n As Integer, wdays As String, datehold As Date, dteFlag As Boolean
dtmEmpStart = Format(dtmEmpStart, "Medium Date")
dtmEmpEnd = Format(dtmEmpEnd, "Medium Date")
dteStartDate = Format(dteStartDate, "Medium Date")
dteEndDate = Format(dteEndDate, "Medium Date")

dteFlag = False
'Reverse the dates if they were input backwards
If dteStartDate > dteEndDate Then
   datehold = dteStartDate
   dteStartDate = dteEndDate
   dteEndDate = datehold
   dteFlag = True
End If
'Check to see if the employee start date is after the range start date

If dtmEmpStart > dteStartDate Then
    dteStartDate = dtmEmpStart
End If

'Check to see if the employee end ate is before the end date
If dtmEmpEnd < dteEndDate Then
    dteEndDate = dtmEmpEnd
End If


n = 0
'days to exclude (7 & 1 unless other specified)
wdays = pExcl
Do While dteStartDate <= dteEndDate
   n = n + IIf(InStr(wdays, Weekday(dteStartDate)) = 0, 1, 0)
   dteStartDate = dteStartDate + 1
Loop
CalcWkDays2 = n
End Function

Function GetDepartment() As Long
GetDepartment = LngDepartment
End Function

 

[Brianwarnock]

It seems to me that if that works then convert your month to start and end dates and use it.
start Dateserial([Year],[Month],1)
end Dateserial([year],[month]+1,0)

Brian

 

[andy_dyer]

It seems to me that if that works then convert your month to start and end dates and use it.
start Dateserial([Year],[Month],1)
end Dateserial([year],[month]+1,0)

Brian

It works! Thank you so much Brian and also Namliam!