Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Dec 5, 2002

Access 2000 How To's: Calculating Working Days

By David Nishimoto


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
      End If
      GetEndWorkDay = sEndDate
  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 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
    GetNumberOfWorkDays = iWorkDays
  End Function
  1. DateDiff specifics a number of time intervals between two dates.
  2. DateDiff (interal,date1,date2,firstdayofweek,firstweekofyear)

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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM