Date function eliminating weekends/holidays 1

[MTGMAN64]

I have developed a loan tracking database in Access 97. I do a report that tracks the number of days it takes for the file room to receive a file from a loan center. I have two date controls on a form, InterestStartDate and ReceiveDate. I subtract ReceiveDate from InterestStartDate to get the time elapsed. The problem is that the formula counts weekends and holidays. Is there a custom function I can create that will eliminate weekends and holidays when I subtract two date fields? I appreciate your help!

 

[smedvid]

While I have never done this, my suggestion would be to create a table that contains a list of holidays. Keeping in mind that your company may not match standard Holiday schedules.

As for weekends, you should be able ot write a loop routine that parses the date range and loops through each date and checks if it is a DAY=6 or 7, which is Sat and Sun, I believe.

Here is some psuedo code that may help...

ldStartDate=YOURStartDate - 1
ldEndDate=YOUREndDate
lnNumofInvalidDays=0
For Next X+1
ldTempDate = ldStartDate + X

SQL-Check if in Holiday Table - Set Flag

IF DAyofWeek="Sat" or "Sun" or InHolidayTableFlag=True
lnNumofInvalidDays = lnNumofInvalidDays + 1
ELSE
lnNumofValidDays = lnNumofValidDays + 1
ENDIF

If ldTempDate=ldEndDate + 1
Exit
Endif

Next


htwh
Steve

 

[DougP]

you will have to write it.
But the holidays would have to be put in a table which needs to be updated every year.

Date Holiday
1/1/00 New years
5/31/00 Memorial Day
7/4/00 Forth of July
etc.

Then after you get the dates check to make sure its not in the above Table

As far as weekends go you can see which DAY they are both on
If its a SAT. or SUN. then add or subtract.

and the Format function
= Format (now,"WW&quot weeknumber
= Format (now, "w&quot the DAY 0=sunday 1=monday etc.

also look in Help at the DateSerial function and
DiffDate




DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[MichaelRed]

per the preceeding posts, completed function. You will need to construct and maintain the table for the Holidays. The sample below is set up to be used with the function.

Holidays table(tblHolidays), refered to in the function below:


HoliDate HoliName

1/1/00 New Year's Day
1/17/00 Martin Luther King Day
2/2/00 Groundhog Day
2/12/00 Lincon's Birthday
2/14/00 Valentine's Day
2/21/00 President's Day
2/22/00 Washington's Birthday
3/8/00 Ash Wednesday
3/17/00 St. Patrick's Day
4/1/00 April Fool's Day
4/20/00 Passover
4/21/00 Good Friday
5/5/00 Cinco de Mayo
5/14/00 Mother's Day
6/11/00 Pentecost
6/18/00 Father's Day
7/4/00 Independence Day
9/4/00 Labor Day
10/31/00 Halloween
11/11/00 Vetran's Day
11/23/00 Thanksgiving
12/25/00 Christmas
12/31/00 New Year's Eve

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

    'Get the number of workdays between the given dates

    Dim dbs As Database
    Dim rstHolidays As Recordset

    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = CLng(StartDate) To CLng(EndDate)
        Select Case (WeekDay(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
                rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                 Else
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd("d", 1, MyDate)

    Next Idx

    DeltaDays = NumDays

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[jdegeorge]

This is a very concise and compact way to do holiday/weekend math. I've been using a function created by someone else for a few years now, and your version is more streamlined and more accurate. Thanks!