By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Calculate Date Differences Excluding Holidays/Weekends

P: 98
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 "Holidaydate".

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?
Nov 24 '08 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,287
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.RowSource = "SELECT Holidaydate FROM HolidayTable WHERE Holidaydate BETWEEN #" & StartDate & "# AND #" & EndDate & "#;"
numberOfHolidaysToSubtract = listbox.ListCount
Nov 24 '08 #2

GazMathias
Expert 100+
P: 189
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:

Expand|Select|Wrap|Line Numbers
  1. Somefield: Workingdays([Startfield],[Completefield])
If on a form, place an unbound textbox there and then put this in the form's On Current event:

Expand|Select|Wrap|Line Numbers
  1. 'Change all control names to your control names.
  2. If isnull(Me.StartDateControlName) or isnull(Me.EndDateControlName) Then
  3. Me.TextBoxName = "N/A"
  4. Exit Sub
  5. Else
  6. Me.TextBoxName = WorkingDays(Me.StartDateControlName,Me.EndDateControlName)
  7. End If
Place this function inside a module.

Expand|Select|Wrap|Line Numbers
  1. Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
  2. On Error GoTo Err_WorkingDays
  3.  
  4. Dim intCount As Integer
  5. Dim temp As Integer
  6. Dim strWhere As String
  7.  
  8. 'Set same day as 1 <-- your preference. Though you can't work out averages if they are 0.
  9. 'Im not checking if this day is a holiday (though in theory you shouldn't have to!).
  10. If StartDate = EndDate Then
  11. WorkingDays = 1 'Change me to your needs.
  12. Exit Function
  13. End If
  14.  
  15. intCount = 1 ' Now we start counting days. 'If you always want to count the first day, set this to 1.
  16.  
  17. Do Until StartDate = EndDate
  18.  
  19. 'First, we find out if this day is a weekday or a weekend.
  20. 'If weekday, 1 gets added to the number of days.
  21. Select Case Weekday(StartDate)
  22. Case Is = 1, 7
  23. intCount = intCount 'Weekend, so nothing added.
  24. Case Else
  25. intCount = intCount + 1
  26. End Select
  27.  
  28. 'Now, if this day was a holiday, we take it back off again!
  29. strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
  30. If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
  31. intCount = intCount - 1
  32. End If
  33.  
  34. StartDate = StartDate + 1 'We move to the next day.
  35.  
  36. Loop
  37.  
  38. WorkingDays = intCount
  39.  
  40. Exit_WorkingDays:
  41. Exit Function
  42.  
  43. Err_WorkingDays:
  44. MsgBox Err.Description
  45. Resume Exit_WorkingDays
  46.  
  47. End Function
  48.  
Gaz :)
Nov 25 '08 #3

P: 98
i am about to try to use this right now.


Thank you!
Nov 26 '08 #4

P: 98
@GazMathias
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?
Dec 1 '08 #5

P: 98
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
Dec 24 '08 #6

FishVal
Expert 2.5K+
P: 2,653
[quote]
Expand|Select|Wrap|Line Numbers
  1. strWhere = "Holidays=#" & StartDate & "#" 'change fieldname.
  2. If DCount("Holidays", "tbl_holidays", strWhere) > 0 Then 'Change to your field/table names.
  3. intCount = intCount - 1
  4. End If
  5.  
: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.
Dec 24 '08 #7

FishVal
Expert 2.5K+
P: 2,653
@trixxnixon
Just add 7*60+1 minutes to the start date.
DateAdd() function is just for that.

Regards,
Fish
Dec 24 '08 #8

ADezii
Expert 5K+
P: 8,607
@trixxnixon
  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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fExcludeHolidaysAndWeekEnds(dteStartDate As Date, dteEndDate As Date) As Integer
    2. Dim intDayDiff As Integer
    3. Dim intDayCounter As Integer
    4. Dim dteCurrentDate As Date
    5. Dim intWeekEndHolDates As Integer
    6.  
    7. intDayDiff = DateDiff("d", dteStartDate, dteEndDate)
    8.  
    9. For intDayCounter = 0 To intDayDiff
    10.   dteCurrentDate = DateAdd("d", intDayCounter, dteStartDate)
    11.     If Weekday(dteCurrentDate) = 7 Or Weekday(dteCurrentDate) = 1 Or DLookup(dteCurrentDate, "tblHolidays", "[Date] = #" & _
    12.                dteCurrentDate & "#") > 0 Then
    13.       intWeekEndHolDates = intWeekEndHolDates + 1
    14.     End If
    15. Next
    16.  
    17. fExcludeHolidaysAndWeekEnds = intDayDiff - intWeekEndHolDates
    18. End Function
  3. Call the Function ans pass to it the Dates in question.
    Expand|Select|Wrap|Line Numbers
    1. Dim intDaysDiff As Integer
    2.  
    3. intDaysDiff = fExcludeHolidaysAndWeekEnds(#12/1/2008#, #12/31/2008#)
    4. MsgBox intDaysDiff
  4. The Return Value will be the number of Days between the 2 Dates, minus Weekends and Holidays.
Dec 24 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.