Solved

Exclude Sat/Sun in Days Calculation

Posted on 2000-04-03
7
799 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:d_brown
7 Comments
 
LVL 14

Accepted Solution

by:
mgrattan earned 200 total points
ID: 2681571
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
  Dim lngBusinessDays 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)
      lngBusinessDays = rst![Count]
    rst.Close
  db.Close

  DiffBusinessDays = lngWeekdays - lngBusinessDays

End Function
0
 
LVL 1

Expert Comment

by:francois_dion
ID: 2681575
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
 
LVL 1

Expert Comment

by:francois_dion
ID: 2681589
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 14

Expert Comment

by:mgrattan
ID: 2681592
Addition to my comment:

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

Expert Comment

by:hnasr
ID: 2685329
' 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
    Dim aDate As Variant
    Set db = CurrentDb
   
    count_weekends = 0
    aDate = dateFrom
    Do While aDate < DateTo
        x = WeekDay(aDate)
        If x = 1 Or x = 7 Then
            count_weekends = count_weekends + 1
        Else
        End If
        aDate = DateAdd("d", 1, aDate)
    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 Comment

by:d_brown
ID: 2687170
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 2687177
No problem!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now