Calculate Date Differences Excluding Holidays/Weekends

[trixxnixon]

I know this topic is a veritable dead horse, but I have to ask, because I am unable to find something that is close to my scenario that I can completely understand.


I have a start date field and complete date field.
I need to know how many days, minus holidays and weekends are between the two dates. i already have the table of holidays named "holiday table" with the field being named "Holidaydat e".

I need this to output a number into a field for number of days.

Is there a simple way to do this? Most of the code I have seen is a bit beyond my understanding as I am still a novice.

Does anyone have some code that might do this?

[ChipR]

Here's how I'd subtract the holidays, though I haven't looked at any other posts so there may well be a better way:

listbox.RowSour ce = "SELECT Holidaydate FROM HolidayTable WHERE Holidaydate BETWEEN #" & StartDate & "# AND #" & EndDate & "#;"
numberOfHoliday sToSubtract = listbox.ListCou nt

[GazMathias]

Hi,

I wrote this little function and it seems to do what you want. It is fairly customisable so you should be able to tweak it for your needs. It assumes you have a table somewhere with dates to exclude.

If you are using it in a query, then use it like:

Somefield: Workingdays([Startfield],[Completefield])

If on a form, place an unbound textbox there and then put this in the form's On Current event:

'Change all control names to your control names.
If isnull(Me.StartDateControlName) or isnull(Me.EndDateControlName) Then
Me.TextBoxName = "N/A"
Exit Sub
Else
Me.TextBoxName = WorkingDays(Me.StartDateControlName,Me.EndDateControlName)
End If

Place this function inside a module.

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim temp As Integer
Dim strWhere As String

'Set same day as 1 <-- your preference. Though you can't work out averages if they are 0.
'Im not checking if this day is a holiday (though in theory you shouldn't have to!).
If StartDate = EndDate Then
WorkingDays = 1 'Change me to your needs.
Exit Function
End If

intCount = 1 ' Now we start counting days. 'If you always want to count the first day, set this to 1.

Do Until StartDate = EndDate

'First, we find out if this day is a weekday or a weekend.
'If weekday, 1 gets added to the number of days.
Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount 'Weekend, so nothing added.
Case Else
intCount = intCount + 1
End Select

'Now, if this day was a holiday, we take it back off again!
strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
intCount = intCount - 1
End If

StartDate = StartDate + 1 'We move to the next day.

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
MsgBox Err.Description
Resume Exit_WorkingDays

End Function

Gaz :)

[trixxnixon]

thanks gaz,

i am about to try to use this right now.


Thank you!

[trixxnixon]

Follow up question,

Hi,

I wrote this little function and it seems to do what you want. It is fairly customisable so you should be able to tweak it for your needs. It assumes you have a table somewhere with dates to exclude.

If you are using it in a query, then use it like:

Somefield: Workingdays([Startfield],[Completefield])

If on a form, place an unbound textbox there and then put this in the form's On Current event:

'Change all control names to your control names.
If isnull(Me.StartDateControlName) or isnull(Me.EndDateControlName) Then
Me.TextBoxName = "N/A"
Exit Sub
Else
Me.TextBoxName = WorkingDays(Me.StartDateControlName,Me.EndDateControlName)
End If

Place this function inside a module.

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim temp As Integer
Dim strWhere As String

'Set same day as 1 <-- your preference. Though you can't work out averages if they are 0.
'Im not checking if this day is a holiday (though in theory you shouldn't have to!).
If StartDate = EndDate Then
WorkingDays = 1 'Change me to your needs.
Exit Function
End If

intCount = 1 ' Now we start counting days. 'If you always want to count the first day, set this to 1.

Do Until StartDate = EndDate

'First, we find out if this day is a weekday or a weekend.
'If weekday, 1 gets added to the number of days.
Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount 'Weekend, so nothing added.
Case Else
intCount = intCount + 1
End Select

'Now, if this day was a holiday, we take it back off again!
strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
intCount = intCount - 1
End If

StartDate = StartDate + 1 'We move to the next day.

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
MsgBox Err.Description
Resume Exit_WorkingDays

End Function

Gaz :)

Follow up question,

Can the calculation be altered to process if either date is in a different format? like2/18/2008" and the other is 12/1/2008 3:55:53 PM"?


how might i have the program skip a day if the start date (submitted date) was after 4:59 pm?

[trixxnixon]

i can only get this to work some of the time. usually all i get is overflow errors,
i have no idea what i am doing wrong

[FishVal]

[quote]

strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
intCount = intCount - 1
End If

:D

From some unknown to me reasons this code is everywhere in the net.
I just wonder who was the first to suggest this code.

Wouldn't it be more effective to run DCount once using range from StartDate till EndDate as criteria and holiday table filtered from weekends with a simple query as domain instead of running it on each day in the range?

Kind regards,
Fish.

[FishVal]

how might i have the program skip a day if the start date (submitted date) was after 4:59 pm?

Just add 7*60+1 minutes to the start date.
DateAdd() function is just for that.

Regards,
Fish

[ADezii]

i can only get this to work some of the time. usually all i get is overflow errors,
i have no idea what i am doing wrong

1. Create a Table named tblHolidays, with a single Field named DATE (DATE/TIME). Populate this Table with all the Holidays that you wish to exclude, namely 12/25/2008, 1/1/2009, etc.
2. Copy and Paste the following code into a Standard Code Module:
   

   Public Function fExcludeHolidaysAndWeekEnds(dteStartDate As Date, dteEndDate As Date) As Integer
   Dim intDayDiff As Integer
   Dim intDayCounter As Integer
   Dim dteCurrentDate As Date
   Dim intWeekEndHolDates As Integer
   
   intDayDiff = DateDiff("d", dteStartDate, dteEndDate)
   
   For intDayCounter = 0 To intDayDiff
     dteCurrentDate = DateAdd("d", intDayCounter, dteStartDate)
       If Weekday(dteCurrentDate) = 7 Or Weekday(dteCurrentDate) = 1 Or DLookup(dteCurrentDate, "tblHolidays", "[Date] = #" & _
                  dteCurrentDate & "#") > 0 Then
         intWeekEndHolDates = intWeekEndHolDates + 1
       End If
   Next
   
   fExcludeHolidaysAndWeekEnds = intDayDiff - intWeekEndHolDates
   End Function

3. Call the Function ans pass to it the Dates in question.
   

   Dim intDaysDiff As Integer
   
   intDaysDiff = fExcludeHolidaysAndWeekEnds(#12/1/2008#, #12/31/2008#)
   MsgBox intDaysDiff

4. The Return Value will be the number of Days between the 2 Dates, minus Weekends and Holidays.