Full Version: How Do I Eliminate Weekends From My Date Calculations?
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
TeresaMaria
Hello,

I am sure this has been answered many times, but when I ready the response I don't understand how to apply the solutions already given to my database. So I don't know if its an applicatoin issue or if I just have no clue how to start. (I think its because I have no clue how to start)

I have a query that returns records that were entered 3 days before based on a search criteria.

On Monday how do I keep Saturday and Sunday from being "counted" in those 3 days. I only want the query to consider workdays

Any thoughts?

Thanks
Kamulegeya
QUOTE (TeresaMaria @ May 12 2012, 05:14 PM) *
Hello,

I am sure this has been answered many times, but when I ready the response I don't understand how to apply the solutions already given to my database. So I don't know if its an applicatoin issue or if I just have no clue how to start. (I think its because I have no clue how to start)

I have a query that returns records that were entered 3 days before based on a search criteria.

On Monday how do I keep Saturday and Sunday from being "counted" in those 3 days. I only want the query to consider workdays

Any thoughts?

Thanks



Hello

add a calculated column to your query with this expression

CODE
DayOfWeek:Weekday([YourDateFieldHere], 2)  and creteria section of the query add

<=5


If you type in the immediate window
CODE
?Weekday(Date,vbMonday)


You get 6 meaning weekday function return 6 today being a Saturday

try
CODE
?vbMonday
in the immediate window . You get 2. That how i came up with two in expression

Ronald

Alan_G
Hi

Have you looked at Brent's solution in the Code Archive
Gustav
You can use the criteria:

Where ISO_WorkdayDiff([YouDateField], Date()) = 3

and these functions:

CODE
Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date, _
  Optional ByVal booExcludeHolidays As Boolean) _
  As Long

' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' May be freely used and distributed.

' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
'             Option for 5 or 6 working days per week added.
' 2008-06-12. Option to exclude holidays from the count of workdays.
' 2011-06-08. Rewrite using Skip functions.

  Const cbytWorkdaysOfWeek  As Byte = 5
  ' Name of table with holidays.
  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"

  Dim booReverse            As Boolean
  Dim booSameWeekend        As Boolean
  Dim lngWeeks              As Long
  Dim lngDays               As Long
  Dim lngHolidays           As Long
  Dim lngWorkdays           As Long
  Dim strDateFrom           As String
  Dim strDateTo             As String
  Dim strFilter             As String
  
  If WeekDay(datDateFrom, vbMonday) > cbytWorkdaysOfWeek And _
    WeekDay(datDateTo, vbMonday) > cbytWorkdaysOfWeek Then
    ' Both dates are of the same weekend.
    booSameWeekend = True
  End If
  
  Select Case DateDiff("d", datDateFrom, datDateTo)
    Case 0
      ' Zero days.
      Exit Function
    Case 1
      If booSameWeekend Then
        ' Both dates are of the same weekend.
        Exit Function
      End If
    Case -1
      If booSameWeekend Then
        ' Both dates are of the same weekend.
        Exit Function
      Else
        booReverse = True
      End If
    Case Is < -1
      ' Negative count.
      booReverse = True
    Case Else
      ' Positive count.
  End Select
    
  ' Adjust dates to skip weekends and holidays.
  datDateFrom = DateSkipNoneWorkingday(datDateFrom, booReverse)
  datDateTo = DateSkipNoneWorkingday(datDateTo, Not booReverse)
  
  ' Find count of full weeks.
  lngWeeks = DateDiff("w", datDateFrom, datDateTo)
  ' Calculate number of working days between the two weekdays ignoring holidays.
  lngDays = WeekDay(datDateTo, vbMonday) - WeekDay(datDateFrom, vbMonday) _
    + cbytWorkdaysOfWeek * DateDiff("ww", DateAdd("ww", lngWeeks, datDateFrom), datDateTo, vbMonday)
  
  If booExcludeHolidays Then
    strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
    strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
    strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
    lngHolidays = IIf(booReverse, -1, 1) * DCount("*", cstrTableHoliday, strFilter)
  End If
  ' Add number of working days between the weeks of the two dates.
  ' Deduct count of holidays.
  lngWorkdays = lngDays + cbytWorkdaysOfWeek * lngWeeks - lngHolidays
  
  ISO_WorkdayDiff = lngWorkdays

End Function


Public Function DateSkipNoneWorkingday( _
  ByVal datDate As Date, _
  Optional ByVal booReverse As Boolean) _
  As Date

' Purpose: Calculate first working day following/preceding datDate.
'
' 2009-04-12. Gustav Brock, Cactus Data ApS, Copenhagen

  
  Dim datNext As Date
  Dim datTest As Date
    
  datNext = datDate
  Do
    datTest = datNext
    datNext = DateSkipHoliday(datTest, booReverse)
    datNext = DateSkipWeekend(datNext, booReverse)
  Loop Until DateDiff("d", datTest, datNext) = 0
  
  DateSkipNoneWorkingday = datNext

End Function


Public Function DateSkipWeekend( _
  ByVal datDate As Date, _
  Optional ByVal booReverse As Boolean) _
  As Date

' Purpose: Calculate first working day equal to or following/preceding datDate.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' Limitation: Does not count for public holidays.
'
' May be freely used and distributed.
' 1999-07-03, Gustav Brock, Cactus Data ApS, Copenhagen
  
  Const cintWorkdaysOfWeek As Integer = 5

  Dim bytSunday   As Byte
  Dim bytWeekday  As Byte
  
  bytSunday = WeekDay(vbSunday, vbMonday)
  bytWeekday = WeekDay(datDate, vbMonday)
  
  If bytWeekday > cintWorkdaysOfWeek Then
    ' Weekend.
    If booReverse = False Then
      ' Get following workday.
      datDate = DateAdd("d", 1 + bytSunday - bytWeekday, datDate)
    Else
      ' Get preceding workday.
      datDate = DateAdd("d", cintWorkdaysOfWeek - bytWeekday, datDate)
    End If
  End If

  DateSkipWeekend = datDate

End Function


Public Function DateSkipHoliday( _
  ByVal datDate As Date, _
  Optional ByVal booReverse As Boolean) _
  As Date

' Purpose: Calculate first day following/preceding datDate if this is holiday.
'
' 2009-04-12. Gustav Brock, Cactus Data ApS, Copenhagen

  ' Adjust to fit your table of holidays.
  Const cstrHolidayTable  As String = "tblHoliday"
  Const cstrHolidayField  As String = "HolidayDate"
  
  While Not IsNull(DLookup(cstrHolidayField, cstrHolidayTable, cstrHolidayField & " = " & Format(datDate, "\#m\/d\/yyyy\#")))
    datDate = DateAdd("d", 1 - Abs(2 * booReverse), datDate)
  Wend

  DateSkipHoliday = datDate

End Function

/gustav
TeresaMaria
Kamulegeya: your suggestion appears to be the less intimidating for a newbie like myself so I wanted to start with this one.

I am not sure that I was clear enough in my explanation of what I needed..... I will tell you where I am at

I have the calculated field entered and it is returning a number for the day of the week (1=monday, 2=tuesday, 3=wednesday,4=thursday,5=friday, etc...) I am not sure how to apply this to get the desired results

I need to see which records that have the status of "open" have been open for more than 3 days.

Example: if record #1 was entered on Monday 5/7 that is more than 3 days ago so on Monday 5/14 this record should retun in the query.

But record #15 was entered on Friday 5/11 so on Monday 5/14 it will actually only have been entered 1 work day since it was entered - but if the query counts Sat and Sun it will add this record to the query.

I do not want records entered on Thursday to return in the query until Tuesday of the following week. And records entered on Friday I do not want to return until Wednesday of the following week

Any thoughts?

Teresa
TeresaMaria
Gustav Thank you for the code, but I am a newbie and I am not sure what to do with this.... but I am willing to try. Where exactly do I paste this? In a macro??

If you can help me understand I will appreciate it very much

Thanks
TeresaMaria
Gustav I copied your code and criteria and it seems to be working! THANK YOU!!

Will this code be good indefinitly? Or will in time it "expire" and need to have an update?

I added the holiday table and I understand that I will need to keep adding the holidays in there each year for it to be accurate - but will that be all I have to do?
Kamulegeya
QUOTE (TeresaMaria @ May 13 2012, 01:03 AM) *
Kamulegeya: your suggestion appears to be the less intimidating for a newbie like myself so I wanted to start with this one.

I am not sure that I was clear enough in my explanation of what I needed..... I will tell you where I am at

I have the calculated field entered and it is returning a number for the day of the week (1=monday, 2=tuesday, 3=wednesday,4=thursday,5=friday, etc...) I am not sure how to apply this to get the desired results

I need to see which records that have the status of "open" have been open for more than 3 days.

Example: if record #1 was entered on Monday 5/7 that is more than 3 days ago so on Monday 5/14 this record should retun in the query.

But record #15 was entered on Friday 5/11 so on Monday 5/14 it will actually only have been entered 1 work day since it was entered - but if the query counts Sat and Sun it will add this record to the query.

I do not want records entered on Thursday to return in the query until Tuesday of the following week. And records entered on Friday I do not want to return until Wednesday of the following week

Any thoughts?

Teresa



Hello

What happens if you add a criteria under calculated field of <=5?

Ronald

Gustav
> Will this code be good indefinitly? Or will in time it "expire" and need to have an update?

It will work as long Access works.
But you are correct, using this method for holidays as well, will require an updated table of holidays.

/gustav
TeresaMaria
Thank you very much for the code and support
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.