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

Posted on 2009-05-11
Last Modified: 2012-05-06
How to I obtain difference between two dates ,excluding weekends in MS ACCESS query.
Question by:RalphyC
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
LVL 39

Expert Comment

ID: 24358965
Try this.

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

Author Comment

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

Accepted Solution

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


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
    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
        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
            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
    ' convert days to hours
    WorkingHrsHolTbl = WorkingHrsHolTbl * 24
    On Error GoTo 0
    Set Dict = Nothing
End Function

Open in new window

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!

LVL 39

Expert Comment

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.

Author Comment

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

Expert Comment

by:Patrick Matthews
ID: 24364856

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.


LVL 39

Expert Comment

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

LVL 39

Expert Comment

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


Featured Post

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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