Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Calculate Date Differences Excluding Holidays/Weekends

Member
  
Join Date: Sep 2008
Posts: 71
#1: Nov 24 '08
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?

Expert
  
Join Date: Jul 2008
Location: Maryland
Posts: 1,176
#2: Nov 24 '08

re: Calculate Date Differences Excluding Holidays/Weekends

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
GazMathias's Avatar
Expert
  
Join Date: Oct 2008
Location: Bristol, United Kingdom
Posts: 145
#3: Nov 25 '08

re: Calculate Date Differences Excluding Holidays/Weekends

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 :)
Member
  
Join Date: Sep 2008
Posts: 71
#4: Nov 26 '08

re: Calculate Date Differences Excluding Holidays/Weekends

i am about to try to use this right now.


Thank you!
Member
  
Join Date: Sep 2008
Posts: 71
#5: Dec 1 '08

re: Calculate Date Differences Excluding Holidays/Weekends

Quote:

Originally Posted by GazMathias View Post

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

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?
Member
  
Join Date: Sep 2008
Posts: 71
#6: Dec 24 '08

re: Calculate Date Differences Excluding Holidays/Weekends

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's Avatar
Expert
  
Join Date: Jun 2007
Location: Israel
Posts: 2,584
#7: Dec 24 '08

re: Calculate Date Differences Excluding Holidays/Weekends

[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.
FishVal's Avatar
Expert
  
Join Date: Jun 2007
Location: Israel
Posts: 2,584
#8: Dec 24 '08

re: Calculate Date Differences Excluding Holidays/Weekends

Quote:

Originally Posted by trixxnixon View Post

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's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,219
#9: Dec 24 '08

re: Calculate Date Differences Excluding Holidays/Weekends

Quote:

Originally Posted by trixxnixon View Post

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:
    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.
Reply

Tags
date, date math, dateadd, holiday, weekday

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,510 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved