Business Day of the Month 1

[davo]

Hi.<br><br>Is there anyway Access97 can count the number of business days?<br><br>Thanks.<br>Davo

 

[nightrider]

use the weekday() function

 

[davo]

Yes. I know but this shows me the business day and not the number of business days that have passed during the month. <br><br>I should have said how many weekdays in the month! Sorry.<br><br>So you would calc the the number of weeks that have passed in the month.(ie from the total in the year and then the current week of the year). Then figure out how many weekends have passed and deduct it from the total days in the month.<br><br>Very messy. Does someone have something easier?<br><br>Davo

 

[cmmrfrds]

Here is one way to get the business days. Make a function that calculates the business days and returns the number of business days. A primitive example:<br>Function CheckDate() As Integer<br>'-- function returns the business days between 2 dates<br>Dim dt As Date, curMon As Date, stopDay As Date<br>Dim dayCnt As Integer, dayNum<br>''-- pass these dates to the function as parameters, or however you want<br>curMon = #7/1/2000#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;''-- first day of the current month<br>stopDay = #7/18/2000#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;''-- up until this day, which can be last day in month<br>For dt = curMon To stopDay<br>&nbsp;&nbsp;&nbsp;&nbsp;dayNum = Weekday(dt)<br>&nbsp;&nbsp;&nbsp;&nbsp;If (dayNum &lt;&gt; 1 And dayNum &lt;&gt; 7) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dayCnt = dayCnt + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;Business days in Month until today = &quot;; dayCnt<br>Next ''- end for loop<br>CheckDate = dayCnt<br>End Function<br>

 

[MichaelRed]

Two Functions.&nbsp;&nbsp;One just for the current Month, the other for any interval.&nbsp;&nbsp;Please note that BOTH of these count the &quot;last&quot; day (e.g if the current date or end date are 'weekdays', they are included.<br><br>Public Function BusinessDays() As Integer<br><br>&nbsp;&nbsp;&nbsp;&nbsp;'Calculates the Number of Bussiness<br>&nbsp;&nbsp;&nbsp;&nbsp;'days elapsed in the _Current_ Month<br>&nbsp;&nbsp;&nbsp;&nbsp;'Requires no input, includes the current day<br>&nbsp;&nbsp;&nbsp;&nbsp;'in the calculation.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim StartMon As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim StartDay As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim StartYr As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim StartDate As Date<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim LastDate As Date<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Idx As Long<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim WkDay As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim intTemp As Integer<br><br>&nbsp;&nbsp;&nbsp;&nbsp;StartMon = Month(Now())<br>&nbsp;&nbsp;&nbsp;&nbsp;StartDay = 1<br>&nbsp;&nbsp;&nbsp;&nbsp;StartYr = Year(Now)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;StartDate = DateValue(StartMon & &quot;/&quot; & StartDay & &quot;/&quot; & StartYr)<br>&nbsp;&nbsp;&nbsp;&nbsp;LastDate = DateValue(Format(Now(), &quot;mm/dd/yy&quot)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;For Idx = StartDate To LastDate<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WkDay = WeekDay(Format(Idx, &quot;mm/dd/yy&quot)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If (WkDay &lt;&gt; 1 And WkDay &lt;&gt; 7) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;intTemp = intTemp + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br><br>&nbsp;&nbsp;&nbsp;&nbsp;BusinessDays = intTemp<br><br>End Function<br><br>'---------------------------------------------------------<br><br>Public Function BusDaysInInterval(StartDate As Date, EndDate As Date) As Integer<br><br>&nbsp;&nbsp;&nbsp;&nbsp;'Calculates the Business days between the start and End <br>&nbsp;&nbsp;&nbsp;&nbsp;'dates.&nbsp;&nbsp;NOTE: Includes the Last day in the calculation<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Idx As Long<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim intBusDays As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim ThisDay As Date<br><br>&nbsp;&nbsp;&nbsp;&nbsp;For Idx = DateValue(StartDate) To DateValue(EndDate)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ThisDay = WeekDay(Format(Idx, &quot;mm/dd/yy&quot)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If (ThisDay &lt;&gt; 1 And ThisDay &lt;&gt; 7) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;intBusDays = intBusDays + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next Idx<br><br>&nbsp;&nbsp;&nbsp;&nbsp;BusDaysInInterval = intBusDays<br><br>End Function<br><br>