Solved

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

Posted on 2009-05-11
8
800 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 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