Date Difference - only including business days? How to run a query exactly (step by step)

MaeMcG
MaeMcG used Ask the Experts™
on
I am trying to run a date difference query in my Access database. I am new to Access so I am not even sure what the query would be and how exactly I would run it.

I have two fields named Date of Discovery and Date Processed and I need to find the date difference between the two; or how many days are in-between the two. However, I also need it to be only business days  so it does not include weekends when counting, is there anyway to do that? I would like this number difference to appear in my field named # of Days.

What exactly would my query be and where would I save this or how do I run it? The name of my Table is EF&L Table.

THANK YOU!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
You should add / replace the zone for your question to the MS Access zone. This is the excel side of town (you might still get the right answer, but it could take longer)

Thomas

Commented:
I don't think you can't accomplish this with just a query, you're going to need to write some code in order to handle the weekends/holidays.

If you're not looking for Holidays then you might check out this article:
http://office.microsoft.com/en-us/access/HA010550781033.aspx

If you do need holidays then you might look at this article as well:
http://www.tek-tips.com/viewthread.cfm?qid=719764&page=2
This function pasted in a query column should come close.  Does not accomodate holidays.

datediff(d,[Date of Discovery],[Date Processed]) - (datediff(ww,[Date of Discovery],[Date Processed]) * 2)
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No Points wanted>

What Torrwin and whitbacon posted may be as close as you can get without going crazy with Custom Functions or code.
(Again, this is even before you can factor in Holidays!)
:-O

Please remenber that DateDiff, means just that, ...the difference in *dates*, ...not "Working" Dates.

JeffCoachman

Author

Commented:

Public Function FixedHoliday(intYear As Integer, intMonth As Integer, intDay As Integer) As Date

    FixedHoliday = DateSerial(intYear, intMonth, intDay)

End Function

Public Function FloatingHoliday(intYear As Integer, intMonth As Integer, intWeek As Integer, _
    intDayOfWeek As Integer) As Date

    FloatingHoliday = DateSerial(intYear, intMonth, (8 - WeekDay(DateSerial(intYear, intMonth, 1), _
        (intDayOfWeek + 1) Mod 8)) + ((intWeek - 1) * 7))

End Function

Public Function WeekDaysPerMonth(intYear As Integer, intMonth As Integer, _
    intDayOfWeek As Integer) As Integer

    Dim i As Integer
    Dim intTotalDays As Integer
    Dim intWeekDayCount As Integer
   
    intWeekDayCount = 0
    intTotalDays = Day(DateAdd("d", -1, DateSerial(intYear, intMonth + 1, 1)))
   
    For i = 1 To intTotalDays
        If WeekDay(DateSerial(intYear, intMonth, i)) = intDayOfWeek Then
            intWeekDayCount = intWeekDayCount + 1
        End If
    Next i
   
    WeekDaysPerMonth = intWeekDayCount
   
End Function

Public Function EasterDate(intYear As Integer) As Date

    Dim i As Integer
   
    i = (((255 - 11 * (intYear Mod 19)) - 21) Mod 30) + 21
   
    EasterDate = DateSerial(intYear, 3, 1) + i + (i > 48) + 6 - _
        ((intYear + intYear \ 4 + i + (i > 48) + 1) Mod 7)

End Function



This is the code provided inTorrwin's post.

I am a beginner at Access so I will need a step by step walk through if someone could please provide it - thank you!!!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Does the code give you the result you are looking for?

Author

Commented:
I don't know how to run that code... it is extremely complicated, so I haven't run it yet. Where would I put it -- could you help tell me what to do? Many thanks!

Commented:
I had hoped to have some time to get you started with this last night.  But this might help.

Forget about the code for right now.  Basically, that code is for if you don't want to manually update tables which contain holiday data.   A good starting point for you would be to create a table for holidays that has to be manually updated.  Once you are comfortable using this table, then you can use the above code to populate it automatically.  So, to start, I would create a table with two fields: Holiday_Name and Holiday_Date.  Then I would manually enter all of the holidays.

Do you have any forms in your Access database yet, or are you only working with the query right now?

Commented:
Assuming you have your Weekend Query and Holiday table set up you can create a query that combines them.  Create a subquery that gets the count of Holidays between your start and end dates and subtract this count from your Weekend Query.

An example of your subquery might be:

SELECT Count(Holiday_Date)
FROM Holidays
WHERE Holiday_Date BETWEEN Date_of_Discovery AND Date_Processed;

If this doesn't make sense just let me know.

Commented:
Did this solve your problem?  This has been inactive for some time now.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Use the function below.

/gustav
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.
 
  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 bytSunday             As Byte
  Dim intWeekdayDateFrom    As Integer
  Dim intWeekdayDateTo      As Integer
  Dim lngDays               As Long
  Dim datDateTemp           As Date
  Dim strDateFrom           As String
  Dim strDateTo             As String
  Dim lngHolidays           As Long
  Dim strFilter             As String
  
  ' Reverse dates if these have been input reversed.
  If datDateFrom > datDateTo Then
    datDateTemp = datDateFrom
    datDateFrom = datDateTo
    datDateTo = datDateTemp
  End If
  
  ' Find ISO weekday for Sunday.
  bytSunday = WeekDay(vbSunday, vbMonday)
  
  ' Find weekdays for the dates.
  intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)
  intWeekdayDateTo = WeekDay(datDateTo, vbMonday)
  
  ' Compensate weekdays' value for non-working days (weekends).
  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
  
  ' Calculate number of working days between the two weekdays, ignoring number of weeks.
  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
  ' Add number of working days between the weeks of the two dates.
  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
  
  If booExcludeHolidays And lngDays > 0 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 = DCount("*", cstrTableHoliday, strFilter)
  End If
  
  ISO_WorkdayDiff = lngDays - lngHolidays
 
End Function

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial