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.

-- 
croy

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 & "#")

-- 
Dave Hargis, Microsoft Access MVP


"croy" wrote:

> 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.
> 
> -- 
> croy
> 

On Thu, 18 Oct 2007 11:17:02 -0700, Klatuu
<Klatuu@discussions.microsoft.com> wrote:

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


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
>-----


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 & "#")


Haven't even got to the holidays part yet!

-- 
Thanks again,
croy

See below
-- 
Dave Hargis, Microsoft Access MVP


"croy" wrote:

> On Thu, 18 Oct 2007 11:17:02 -0700, Klatuu
> <Klatuu@discussions.microsoft.com> wrote:
> 
> 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
> 
> 
> 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...???

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.
> 
> 
> >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
> >-----
> 
> 
> Would this work for start and end dates that are a few years
> apart?

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 & "#")
> 
> 
> Haven't even got to the holidays part yet!
> 
> -- 
> Thanks again,
> croy
> 
>