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

How to I obtain difference between two dates ,excluding weekends in MS ACCESS query.
###### Who is Participating?

x

Commented:
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
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
0

Commented:
Try this.

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

Author Commented:
Is there any way to exclude HOLIDAYS?
0

Commented:
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 Commented:
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

Commented:
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

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

Brandon
0

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

Brandon
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.