Access 2000 How To's: Calculating Working Days
December 5, 2002
This article explains how to use Access 2000 date functions when determining 1) the number of work days representing an interval between two dates, and 2) the projected end working date calculated from a start date and number of hours worked.
The user enters in a start date and actual hours as parameters in the GetEndWorkDay function. The function calculates the date in the future the work should be completed. So, if today is 12/2/2002 and the actual hours is 12, the projected end date would be 12/3/2002.
The second part uses the GetNumberOfWorkDays function to determine what percent of the estimated time actual time represents. Estimated work days is the time interval to complete a task based on a start and end date. Comparing estimated to actual time, we can provide the percent of work completed both under or over allocated percents.
Public Function GetEndWorkDay(sStartDate, sHours) Dim iHoursToDays Dim iCount Dim bFlag Dim sEndDate Dim sCheckDate Dim iFoundCount Dim sDay 'Assume an eight hour day iHoursToDays = round(sHours / 8,0) sEndDate = sStartDate If iHoursToDays > 1 Then bFlag = False iCount = 0 iFoundCount = 0 Do While bFlag = False iCount = iCount + 1 sCheckDate = DateAdd("d", iCount, sStartDate) sDay = Weekday(sCheckDate) If sDay <> 1 And sDay <> 7 Then sEndDate = sCheckDate iFoundCount = iFoundCount + 1 End If If iFoundCount >= iHoursToDays Then Exit Do End If Loop End If GetEndWorkDay = sEndDate End Function
Public Function GetNumberOfWorkDays(sStartDate, sEndDate) Dim iDays Dim iWorkDays Dim sDay Dim i iDays = DateDiff("d", sStartDate, sEndDate) iWorkDays = 0 For i = 0 To iDays 'First day of the week is sunday sDay = Weekday(DateAdd("d", i, sStartDate)) If sDay <> 1 And sDay <> 7 Then iWorkDays = iWorkDays + 1 End If Next GetNumberOfWorkDays = iWorkDays End Function