Business Days , Excluding weekends and Holidays

[aarongregory2821]

Here is a function that I made to replace much larger clunky ones. I needed to count the minutes between a start and ending date. Used as VBA in Access. There is a smarter way to handle the vacation days than I did below, but I was in a hurry to test the other functions. I would put vacation days in a table, check the year of the checked dates, query the table with vacation years matching THAT, and dynamically check those days... but not now....

'///////////////////////////////////////

Option Compare Database

Function timeconvert()

Dim FirstDate As Date
Dim SecondDate As Date
Dim grosscount As Double
Dim grossseconds As Double
Dim grossminutes As Double
Dim grosshours As Double
Dim grossdays As Double

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * from SLA where [Date Resolved] IS NOT NULL ")

With rs
Do Until .EOF

FirstDate = rs![Date Created]
SecondDate = rs![Date Resolved]


grosscount = "0"
grosscount = DateDiff("s", FirstDate, SecondDate)
grossseconds = grosscount

For z = FirstDate To SecondDate
'gots ta visualize a smarter way to do this vacation thing, but this works
If Weekday(z) = 1 Then
grossseconds = grossseconds - 86400
End If

If Weekday(z) = 7 Then
grossseconds = grossseconds - 86400
End If

If z = "8/29/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "8/28/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "8/4/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "01/01/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "05/26/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "07/04/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "09/01/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "11/27/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "11/28/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "12/25/2003" Then
grossseconds = grossseconds - 86400
End If

If z = "12/26/2003" Then
grossseconds = grossseconds - 86400
End If

Next

grossminutes = grossseconds / 60
grosshours = grossseconds / 3600
grossdays = grossseconds / 86400



.Edit
!resdays = grossdays
!reshours = grosshours
!resminutes = grossminutes
' Add Data For other
' Fields Here
.Update

rs.MoveNext

Loop
End With

End Function


'//////////////////////////////////
Aaron
aaron@aarongregory.net
http://www.aarongregory.net