Access 2000 Calculating Working Days

				
Author David Nishimoto
davepamn@relia.net
Articles: Calculating Work days and Project Completion Dates


This article explains how to use Access 2000 date functions when determining 1) the number of work days representing an interval between two dates 2) the projected end working date calculated from a start date and number of hours worked. 

The GetEndWorkDay function was used in Schedule Extreme Pro. The user enters in a start date and actual hours as parameters to the function. The GetEndWorkDay calculates the date in the future the work was completed. So, if today was 10/2/2002 and the actual hours was 12. The actual end date would be 10/3/2002.
Part two includes using the GetNumberOfWorkDays function. The problem is determine what percent of estimated time, actual time represents. Estimated work days is the time interval beto complete a task based on a start and end date. Comparing estimate to actual, I can provide the percent of work completed both under or over allocated percents.

1. DateDiff specifics a number of time intervals between two dates.
2. DateDiff (interal,date1,date2,firstdayofweek,firstweekofyear)
	interval 
		yyyy	= year
		q	= quarter
		m	= month
		y	= day of year
		d	= day
		w	= weekday
		ww	= week
		h	= hour
		n	= minute
		s	= second
	date1 and date2 are used to calculate the interval
	
	firstdayofweek is sunday unless specified
	firstweekofyear is jan 1 unless specified
3. Weekday returns a number representing the day of the week.
	return values are
		Sunday		= 1
		Monday 	= 2			
		Tuesday	= 3
		Wednesday	= 4
		Thursday	= 5
		Friday		= 6
		Saturday	= 7
4. First determine the number of day between the two dates. Calculating the number of
work days is done by not adding Saturday and Sunday dates. A query extract from a table contain
all holidays for the year can also be added for increased accuracy.

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

1. DateAdd returns a date to which a specific 
time interval has been added. In this sample the 
interval is "Day".
	List of Interval Settings:
		yyyy 	= year
		q 	= quarter
		m	= month
		y	= day of year
		d	= day
		w	= weekday
		ww	= week
		h	= hour
		n	= minute
		s	= second
2. Increment through a range of possible work days to find the end work date. The
number of work days is determined by dividing the hours by eight. This assumes
an eight hour work day. Ignore saturday and sunday as a work day. Once
the number of found work days equals the work day interval, stop and return 
the date as the final work date.

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