Exclude Sat/Sun in Days Calculation

I have an Access dbase for order shipments set up with a table that includes the NeedByDate and ShipDate.  I need to calculate the number of days each shipment is early/late based on those 2 dates, but, I want to exclude Saturdays and Sundays in the calculation.  How can I accomplish this?
Example:  NeedByDate=4/7/00 and ShipDate=4/10/00, then shipment was only 1 day late, since sat/sun aren't included.
Who is Participating?

Commented:
Here is a function that will calculate the number of business days between each date provided.  In addition, it references a table that you can add to your database called "tblHolidays" where you can store the observed holidays for the year and have those taken into consideration as well.  If you don't need the additional functionality, just remove the section that searches for the holidays in the tblHolidays table and remove the parameter from the function.

Function DiffBusinessDays(datDay1 As Date, datDay2 As Date, strHolidayTbl As String, strHolidayField As String) As Long
' Comments  : Returns the number of business days between two dates
'             The days are rounded down -- it takes 24 hours to make a day.
'             Weekend dates (Saturday and Sunday) and holidays are not counted.
' Parameters: datDay1 - first (earlier) date/time (subtracted from datDay2)
'             datDay2 - second (later) date/time
'             strHolidayTbl - name of holiday table
'             strHolidayField - field name of holiday dates in the holiday table
' Returns   : Number of whole business days between two dates
'             (Returns negative days if datDay1 is after datDay2)
'
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim strField As String
Dim lngWeekdays As Long

' Calculate number of weekdays between two dates:
lngWeekdays = DiffWeekdays(datDay1, datDay2)

Set db = CurrentDb()
strField = "[" & strHolidayTbl & "].[" & strHolidayField & "]"
strSQL = "SELECT DISTINCTROW Count(" & strField & ") AS Count"
strSQL = strSQL & " FROM [" & strHolidayTbl & "]"
strSQL = strSQL & " WHERE ((" & strField

If datDay1 <= datDay2 Then
strSQL = strSQL & ">=#" & datDay1 & "# And "
strSQL = strSQL & strField & "<#" & datDay2 & "#));"
Else
strSQL = strSQL & ">=#" & datDay2 & "# And "
strSQL = strSQL & strField & "<#" & datDay1 & "#));"
End If

Set rst = db.OpenRecordset(strSQL)
rst.Close
db.Close

End Function
0

Commented:
Although I can't think of a way to do it directly with Access' function, it isn't difficult to whip up a little something to do this...

Public Function WorkDaysLate(NeedByDate As Date, ShipDate As Date) As Long
Dim lDiff As Long
Dim dtmSkipper As Date

lDiff = 0
If (VarType(NeedByDate) = vbDate And VarType(ShipDate) = vbDate) Then
If NeedByDate < ShipDate Then
For dtmSkipper = NeedByDate + 1 To ShipDate
If WeekDay(dtmSkipper) <> vbSunday And _
WeekDay(dtmSkipper) <> vbSaturday Then _
lDiff = lDiff + 1
Next
End If
End If

WorkDaysLate = lDiff
End Function

This little function simply skips through the days, checks if they're not sundays or saturdays to return the number of days late, or a flat zero if the dates don't indicate lateness...

>:) If you need to set it into a query, just send the column names of the select query into the function, easy as pie.
0

Commented:
Although I can't think of a way to do it directly with Access' function, it isn't difficult to whip up a little something to do this...

Public Function WorkDaysLate(NeedByDate As Date, ShipDate As Date) As Long
Dim lDiff As Long
Dim dtmSkipper As Date

lDiff = 0
If (VarType(NeedByDate) = vbDate And VarType(ShipDate) = vbDate) Then
If NeedByDate < ShipDate Then
For dtmSkipper = NeedByDate + 1 To ShipDate
If WeekDay(dtmSkipper) <> vbSunday And _
WeekDay(dtmSkipper) <> vbSaturday Then _
lDiff = lDiff + 1
Next
End If
End If

WorkDaysLate = lDiff
End Function

This little function simply skips through the days, checks if they're not sundays or saturdays to return the number of days late, or a flat zero if the dates don't indicate lateness...

>:) If you need to set it into a query, just send the column names of the select query into the function, easy as pie.
0

Commented:

You will need this function as well in order for the previously mentioned function to work correctly:

Function DiffWeekdays(datDay1 As Date, datDay2 As Date) As Long
' Comments  : Returns the number of weekdays between two dates
'             The days are rounded down -- it takes 24 hours to make a day.
' Parameters: datDay1 - first (earlier) date/time (subtracted from datDay2)
'             datDay2 - second (later) date/time
' Returns   : Number of whole business days between two dates
'             (Returns negative days if datDay1 is after datDay2)
'
Dim lngDays As Long
Dim lngWeeks As Long
Dim datFirstDate As Date
Dim datLastDate As Date
Dim datNewDate As Date
Dim intDirection As Integer

If datDay1 < datDay2 Then
datFirstDate = datDay1
datLastDate = datDay2
intDirection = 1
Else
datFirstDate = datDay2
datLastDate = datDay1
intDirection = -1
End If

' Subtract weekends
lngWeeks = Fix(Fix(datLastDate - datFirstDate) / 7)
lngDays = lngWeeks * 5

datNewDate = CDate(datFirstDate) + lngWeeks * 7

While datNewDate < datLastDate
datNewDate = datNewDate + 1
If datNewDate <= datLastDate Then

' Don't count days when new day is Sunday or Monday.
' (When new day is Saturday, you are actually counting Friday)
If WeekDay(datNewDate) <> 1 And WeekDay(datNewDate) <> 2 Then
lngDays = lngDays + 1
End If

End If
Wend

DiffWeekdays = intDirection * lngDays

End Function
0

Commented:
' here is a try:

' you need to have a table called tblRestarts_weekends: the name was used to answer another EE question.  You can replace with ur own table.
' one field,  weekenddate as date/time : short date

' you need a function to count the number of sats/sundays

' button, dates passed from text fields, or any other way.
' to calculate the number of saturdays/sundays between dateFrom and dateTo

Private Sub count_weekeends_Click()
Dim no_of_weekend_days As Integer
no_of_weekend_days = count_weekends(Me!NeedByDate, Me!ShipDate)
End Sub

' here is the function: count_weekends(Me!dateFrom, Me!DateTo)
' in a module

Public Function count_weekends(dateFrom As Variant, DateTo As Variant) As Variant
Dim db As Database
Dim rs As Recordset
Dim x As Variant
Set db = CurrentDb

count_weekends = 0
If x = 1 Or x = 7 Then
count_weekends = count_weekends + 1
Else
End If
Loop
End Function

' You can find the no of days late as:

no_of_days_late = DateDiff ("d", NeedByDate, ShipDate) - no_of_weekend_days

' ----
'Using a query: here are the querey fields:

NeedByDate            ShipDate           days_late: DateDiff("d", [NeedByDate], [ShipDate]) - count_weekends([NeedByDate], [ShipDate])
0

Author Commented:
Actually I had found some code elsewhere, and finally got it working right about the time I got your answer.  It is basically the same as your suggestion.  Thank you for your help and quick response!
0

Commented:
No problem!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.