Overview
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.
GetEndWorkDay Function
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
- 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
- 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 work days. Once
the number of found work days equals the work day interval, stop and return
the date as the final work date.
GetNumberofWorkDays 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
-
DateDiff specifics a number of time intervals between two dates.
- 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
- 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
- First determine the number of days 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 containing
all the holidays for the year could also be added for increased accuracy.
Back to Access 2000 How To's Series Home