Solved

How to get the difference between two dates ,excluding weekends in MS ACCESS query

Posted on 2009-05-11
8
797 Views
Last Modified: 2012-05-06
How to I obtain difference between two dates ,excluding weekends in MS ACCESS query.
0
Comment
Question by:RalphyC
  • 4
  • 2
  • 2
8 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24358965
Try this.

select datediff("day", date1,date2)-(datediff("week",date1,date2)*2)
from SomeTable
0
 

Author Comment

by:RalphyC
ID: 24359297
Is there any way to exclude HOLIDAYS?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24359799
Hello RalphyC,

THe following function is handy.  It is designed to return "working hours", with the work days and work times,
allowing for a "holiday" table.  By tweaking the arguments it can track wholes.

Use it in a query like this:

SELECT StartDate, EndDate, WorkingHrsHolTbl(StartDate, EndDate, 0, 1, "23456", "tblHolidays", "HolidayDate") / 24 AS NetWorkingDays
FROM SomeTable

Regards,

Patrick
Function WorkingHrsHolTbl(StartAt As Date, EndAt As Date, WorkStart As Date, WorkEnd As Date, Workdays As String, _
    Optional HolidayTblName As String = "", Optional HolidayDateColName As String = "")
    
    ' This function is intended for use in Access, in which you may have a table that defines
    ' holidays.  Holidays are always considered non-working days, and override the normal business
    ' days provided in the Workdays argument
    
    ' Requires reference to DAO library!
    
    ' Function calculates working hours available within a specified datetime range, allowing for
    ' scheduled working hours, non-working days, and holidays (if desired)
    
    ' Workdays specifies days employees normally work.  For example, to use Mon - Fri, use 23456.
    ' To do just Tue & Thu, use 35; etc.
    
    ' assumes scheduled working hrs are the same on each working day!
    
    Dim Counter As Long
    Dim Dict As Object
    Dim x As Variant
    Dim y As Variant
    Dim Days(1 To 7) As Boolean
    Dim WorkThisDay As Boolean
    Dim HolThisDay As Boolean
    Dim DateToday As Date
    Dim DayStart As Date
    Dim DayEnd As Date
    Dim rs As DAO.Recordset
    
    ' array indicates whether that weekday is a regular workday.  Initialize to False
    Days(1) = False
    Days(2) = False
    Days(3) = False
    Days(4) = False
    Days(5) = False
    Days(6) = False
    Days(7) = False
    
    ' populate array with results from Workdays argument
    For Counter = 1 To Len(Workdays)
        Days(Val(Mid(Workdays, Counter, 1))) = True
    Next
    
    On Error GoTo Cleanup
    
    ' populate holiday array
    If HolidayTblName <> "" And HolidayDateColName <> "" Then
        ' test for brackets around table/column names, just in case they are needed and user omitted them
        If Left(HolidayTblName, 1) <> "[" Then HolidayTblName = "[" & HolidayTblName
        If Right(HolidayTblName, 1) <> "]" Then HolidayTblName = HolidayTblName & "]"
        If Left(HolidayDateColName, 1) <> "[" Then HolidayDateColName = "[" & HolidayDateColName
        If Right(HolidayDateColName, 1) <> "]" Then HolidayDateColName = HolidayDateColName & "]"
        Set rs = CurrentDb.OpenRecordset("SELECT " & HolidayDateColName & " FROM " & HolidayTblName
        Set Dict = CreateObject("Scripting.Dictionary")
        Do Until rs.EOF
            If Not Dict.Exists(Format(rs.Fields(0), "m/d/yyyy")) Then
                Dict.Add Format(rs.Fields(0), "m/d/yyyy"), Format(rs.Fields(0), "m/d/yyyy")
            End If
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
    End If
    
    'loop through days in datetime range
    For Counter = Int(StartAt) To Int(EndAt)
        DateToday = CDate(Counter)
        ' determine if regular workday
        WorkThisDay = Days(Weekday(DateToday, vbSunday))
        ' determine if holiday
        If IsMissing(Holidays) Then
            HolThisDay = False
        Else
            If Dict.Exists(Format(DateToday, "m/d/yyyy")) Then HolThisDay = True Else HolThisDay = False
        End If
        ' if regular workday and not a holiday, figure out hrs from that day
        If WorkThisDay And Not HolThisDay Then
            ' starts and ends on same day
            If Int(StartAt) = Int(EndAt) Then
                DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
                DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
                WorkingHrsHolTbl = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            ' first day, if first day <> last day
            ElseIf Counter = Int(StartAt) Then
                DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
                DayEnd = WorkEnd
                WorkingHrsHolTbl = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            ' days in between start and end, if any
            ElseIf Counter > Int(StartAt) And Counter < Int(EndAt) Then
                WorkingHrsHolTbl = WorkingHrsHolTbl + CDbl(WorkEnd - WorkStart)
            ' last day, if first day <> last day
            ElseIf Counter = Int(EndAt) Then
                DayStart = IIf(CDate(EndAt - Int(EndAt)) > WorkStart, WorkStart, CDate(EndAt - Int(EndAt)))
                DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
                WorkingHrsHolTbl = WorkingHrsHolTbl + IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            End If
        End If
    Next
    
    ' convert days to hours
    WorkingHrsHolTbl = WorkingHrsHolTbl * 24
    
Cleanup:
    On Error GoTo 0
    Set Dict = Nothing
    
End Function

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24361197
You would need to have a list of holidays in a table.  but if you want holidays excluded, I'd suggest a new question.  The original question was asked, and I believe answered, by me.
0
 

Author Comment

by:RalphyC
ID: 24364711
So far so good, but when executing the above Function, I received, Complie Error, Varable Not Defined in the following line;

If IsMissing(Holidays) Then' it appears Holidays variable is not defined
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24364856
RalphyC,

I was glad to have helped.  That said, I think that in this case a split was warranted: Brandon did answer the
original question, and my suggestion was really only needed to address the followup on excluding holidays
as well.

I suggest that you use the 'request attention' link to ask the Mods to reopen the question so that you may
reenter it as a split.

Regards,

Patrick
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24365082
Thank you Patrick.  I would have to agree with you on this case.

Brandon
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24365125
RalphyC,
   On your request, we could have this question re-opened if you feel that matthewspatrick and myself have a valid point.

Brandon
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

823 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