Count of Weekdays Between Two Dates

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

Task:

With a given start date and end date, print a list of Months
showing a count of weekdays and weekend days in each.

It seemed like such a simple request... but how to get
started? If I already had a table of all dates between the
start and end dates, I'd have it whipped.

Any thoughts appreciated.
 
Here are some ideas you can use for this. First, you need to know how may
days are in the month. Then you can use the function below to return the
number of work days in the month. The subtract the work days from the number
of days, and it will return the week end days.

To Get the last day of the month:

Dim dtmFistDay as Date 'The First Day of a month
Dim dtmLastDay as Date 'The Last Day of a month
Dim lngTotDays As Long 'Total number of days in the month
Dim lngWorkDays as Long 'Work days in the month
Dim lngWkendDays As Long 'Number of Week End days

dtmFirstDay = DateSerial(Year(Date),Month(Date),1)
'Returns the last day of the month
dtmlastday = DateSerial(Year(dtmFirstDay),Month(dtmFirstDay)+1,0)
lngTotDays = DateDiff("d",dtmFirstDay,dtmLastDay) + 1
lngWorkDays = CalcWorkDays(dtmFirstDay,dtmLastDay)
lngWkendDays = lngTotDays - lngWorkDays

Now the function:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
-----

This, of course, does not include any holidays. If you need to count
holidays, you will need a holiday table and you can use something like this
to count the holidays in the month:

CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" & dtmFirstDay & "# And #" & dtmLastDay & "#")
 
On Thu, 18 Oct 2007 11:17:02 -0700, Klatuu

Thanks to Klatuu. I'm trying to get my head around that.
See below.
Here are some ideas you can use for this. First, you need to know how may
days are in the month. Then you can use the function below to return the
number of work days in the month. The subtract the work days from the number
of days, and it will return the week end days.

To Get the last day of the month:

Dim dtmFistDay as Date 'The First Day of a month
Dim dtmLastDay as Date 'The Last Day of a month
Dim lngTotDays As Long 'Total number of days in the month
Dim lngWorkDays as Long 'Work days in the month
Dim lngWkendDays As Long 'Number of Week End days

dtmFirstDay = DateSerial(Year(Date),Month(Date),1)
'Returns the last day of the month
dtmlastday = DateSerial(Year(dtmFirstDay),Month(dtmFirstDay)+1,0)
lngTotDays = DateDiff("d",dtmFirstDay,dtmLastDay) + 1
lngWorkDays = CalcWorkDays(dtmFirstDay,dtmLastDay)
lngWkendDays = lngTotDays - lngWorkDays

Now the function:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
Click to expand...


Hmmm. The number of days between start and end, minus the
number of weeks who's start is Saturday, plus the number of
weeks who's start is Sunday, plus 1...???


There's definitely something I'm not understanding or
interpreting right.

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
-----
Click to expand...


Would this work for start and end dates that are a few years
apart?

This, of course, does not include any holidays. If you need to count
holidays, you will need a holiday table and you can use something like this
to count the holidays in the month:

CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" & dtmFirstDay & "# And #" & dtmLastDay & "#")
Click to expand...


Haven't even got to the holidays part yet!
 
See below
--
Dave Hargis, Microsoft Access MVP


croy said:
On Thu, 18 Oct 2007 11:17:02 -0700, Klatuu

Thanks to Klatuu. I'm trying to get my head around that.
See below.



Hmmm. The number of days between start and end, minus the
number of weeks who's start is Saturday, plus the number of
weeks who's start is Sunday, plus 1...???
Click to expand...

No, it returns the number of days that are not Saturday or Sunday between
the two dates. Adding one includes all the days.
There's definitely something I'm not understanding or
interpreting right.




Would this work for start and end dates that are a few years
apart?
Click to expand...

Yes. calcworkdays(#1/1/2000#,date) returns 2036 (not including holidays
because I don't have a holiday table in the db i have open right now.
This, of course, does not include any holidays. If you need to count
holidays, you will need a holiday table and you can use something like this
to count the holidays in the month:

CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" & dtmFirstDay & "# And #" & dtmLastDay & "#")
Click to expand...


Haven't even got to the holidays part yet!
Click to expand...
 
Back
Top