Counting Days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to count week days in a range entered by the user. How can I exclude the weekend days? Right now my code is very simple just [enddate]-[startdate]. Which leads to inaccurate results if the range is large.
 
Copy the code from this web page into a new module.

http://www.mvps.org/access/datetime/date0006.htm

Call the function:

Text1 = Work_Days ([startdate], [enddate])

-----Original Message-----
I'm trying to count week days in a range entered by the
user. How can I exclude the weekend days? Right now my
code is very simple just [enddate]-[startdate]. Which
leads to inaccurate results if the range is large.
 
Hi

I've just did a quick hack for you. Here's the code that
you can use. Did a quick test in Excel VBA. Should work
in any VBA (Access, Excel, etc.)

'--- COPY FROM HERE -----------

Function GetDayType(dd As Date) As Byte

'--- This function computes day type based on given
Date (dd)
' Return Codes:
' 1 - Weekday (Monday to Friday)
' 2 - Saturday
' 3 - Sunday

If Weekday(dd, 2) >= 1 And Weekday(dd, 2) <= 5 Then
GetDayType = 1
ElseIf Weekday(dd, 2) = 6 Then
GetDayType = 2
ElseIf Weekday(dd, 2) = 7 Then
GetDayType = 3
End If
End Function

Sub Simulate_Get_Weekdays()
Dim StartDate As Date
Dim EndDate As Date
Dim j As Integer '-- day difference
Dim i As Integer '-- for loop index
Dim k As Integer '-- counter for weekdays

StartDate = #11/1/2003#
EndDate = #11/15/2003#

j = DateDiff("d", StartDate, EndDate)
k = 0

For i = 1 To j

'--- Tip: If you use different return code, you
can count weekends too!
If (GetDayType(DateSerial(Year(StartDate), Month
(StartDate), Day(StartDate) + i)) = 1) Then
k = k + 1
End If
Next i

MsgBox k '-- number of weekdays
End Sub

'---- CODE END HERE---

Enjoy!


-----Original Message-----
I'm trying to count week days in a range entered by the
user. How can I exclude the weekend days? Right now my
code is very simple just [enddate]-[startdate]. Which
leads to inaccurate results if the range is large.
 
Back
Top