Solved

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

Posted on 2009-05-11
8
792 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now