473,287 Members | 1,492 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,287 developers and data experts.

Adding business days with configurable weekend days and holidays

Seth Schrock
2,965 Expert 2GB
Introduction
While I have been able to find an example of how to calculate the number of business days between two dates, I haven't been able to find a published method to add business days. Excel has recently added this function, but Microsoft has yet to give it to the Access world. So, I have undertaken this challenge. The function that I have created allows the user to specify what days are not included in the count (for weekends) and it also allows for a list of holidays to be excluded as well. Holidays must be entered in a table for this to work. I have created my own method for automatically getting this information from the Internet, but that is a subject for another post.

Tables
The best method that I could come up with was to have a list of available dates and then to count down the records for the specified number of days. This means that we need a list of dates. Instead of having to type them all into a table, we will let Access take care of this problem. All we need is a table with a single field in it:
Expand|Select|Wrap|Line Numbers
  1. tbl_ExpansionDigits
  2. Digit
Now add 10 records with the values 0 to 9

We also need a table of holidays. My table has a date field and a name field so that I can list the holiday name if I want to.
Expand|Select|Wrap|Line Numbers
  1. dbo_Holidays
  2. hDate
  3. hName
That is it for tables

Queries
We actually only need one query. This is what performs the magic of giving us dates. Mine is named qryPossibleDates.
Expand|Select|Wrap|Line Numbers
  1. SELECT Cdate([tbl_expansiondigits_1]![Digit]
  2.    +[tbl_expansiondigits_2]![Digit]*10
  3.    +[tbl_expansiondigits_3]![Digit]*100
  4.    +[tbl_expansiondigits_4]![Digit]*1000 
  5.    +[tbl_expansiondigits_5]![Digit]*10000) AS CalcDate, Weekday(CalcDate) AS WeekDayNumber
  6. FROM tbl_expansiondigits AS tbl_expansiondigits_1, tbl_expansiondigits AS tbl_expansiondigits_2, tbl_expansiondigits AS tbl_expansiondigits_3, tbl_expansiondigits AS tbl_expansiondigits_4, tbl_expansiondigits AS tbl_expansiondigits_5
  7. WHERE ((([tbl_expansiondigits_1]![Digit]
  8.    +[tbl_expansiondigits_2]![Digit]*10
  9.    +[tbl_expansiondigits_3]![Digit]*100
  10.    +[tbl_expansiondigits_4]![Digit]*1000
  11.    +[tbl_expansiondigits_5]![Digit]*10000)>=36000));
This gives us dates from 7/24/1998 to 10/13/2173 (a total of 64,000 days) plus the weekday number (1 for Sunday, 7 for Saturday).


Code
First we are going to need a function to convert a binary-like number to a decimal value. I use this to for binary flags to know which days to exclude from the count.
Expand|Select|Wrap|Line Numbers
  1. Private Function ConvertToDecimal(lngBinary As Long) As Long
  2. Dim strNumber As String
  3. Dim i As Integer
  4. Dim lngAccumulator As Long
  5. Dim n As Integer
  6. Dim s As String
  7. Dim p As Integer
  8.  
  9. strNumber = Format(lngBinary, "00000000")
  10. p = 1
  11. For i = 8 To 1 Step -1
  12.     s = Mid(strNumber, p, 1)
  13.     If s = "1" Then
  14.         n = CInt(i) - 1
  15.         lngAccumulator = lngAccumulator + 2 ^ n
  16.     End If
  17.     p = p + 1
  18. Next
  19.  
  20. ConvertToDecimal = lngAccumulator
  21.  
  22. End Function
Now for the main function.
Expand|Select|Wrap|Line Numbers
  1. Public Function AddDays(dteStart As Date, intInterval As Integer, lngExcludePattern As Long) As Date
  2. Dim lngExcludeDates As Long
  3. Dim strSelect As String
  4. Dim strWhere As String
  5. Dim strSQL As String
  6. Dim db As DAO.Database
  7. Dim rst As DAO.Recordset
  8. Dim i As Integer
  9.  
  10. If lngExcludePattern <> 0 Then
  11.     lngExcludeDates = ConvertToDecimal(lngExcludePattern)
  12.     strSelect = "SELECT CalcDate As AddedDate FROM qryPossibleDates"
  13.     strWhere = "CalcDate = #" & dteStart & "# Or (CalcDate >= #" & dteStart & "# And CalcDate Not In(SELECT hDate FROM dbo_Holidays WHERE hDate >= #" & dteStart & "#)"
  14.  
  15.     If (lngExcludeDates And 64) > 0 Then
  16.         strWhere = strWhere & " And WeekDayNumber <> 1"
  17.     End If
  18.  
  19.     If (lngExcludeDates And 32) > 0 Then
  20.         strWhere = strWhere & " And WeekDayNumber <> 2"
  21.     End If
  22.  
  23.     If (lngExcludeDates And 16) > 0 Then
  24.         strWhere = strWhere & " And WeekDayNumber <> 3"
  25.     End If
  26.  
  27.     If (lngExcludeDates And 8) > 0 Then
  28.         strWhere = strWhere & " And WeekDayNumber <> 4"
  29.     End If
  30.  
  31.     If (lngExcludeDates And 4) > 0 Then
  32.         strWhere = strWhere & " And WeekDayNumber <> 5"
  33.     End If
  34.  
  35.     If (lngExcludeDates And 2) > 0 Then
  36.         strWhere = strWhere & " And WeekDayNumber <> 6"
  37.     End If
  38.  
  39.     If (lngExcludeDates And 1) > 0 Then
  40.         strWhere = strWhere & " And WeekDayNumber <> 7"
  41.     End If
  42.  
  43.     strWhere = strWhere & ")"
  44.     strSQL = strSelect & " WHERE " & strWhere
  45.     Debug.Print strSQL
  46.  
  47.     Set db = CurrentDb
  48.     Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
  49.  
  50.     rst.Move intInterval
  51.  
  52.     AddDays = rst!AddedDate
  53.  
  54.     Set db = Nothing
  55.     rst.Close
  56.     Set rst = Nothing
  57.  
  58. Else
  59.     AddDays = dteStart + intInterval
  60. End If
  61.  
  62. End Function
First, I test for the value in lngExcludePattern. If it is 0, then I'm just counting calendar days and it skips to the bottom. Otherwise, I build a query string that pulls from the query above adding the criteria. You will notice that it starts with CalcDate = #" & dteStart & "#". This is for situations where dteStart is not a day that is included in the query as it does mess with the days involved (I found this out after troubleshooting why my counts were off). It then has the criteria to only show records that are greater than or equal to dteStart and not in the list of holidays, again filtered to be greater than or equal to dteStart (for performance). I then test for the binary flags present to add additional criteria to exclude the days of the week that we don't want included. I then open the recordset and use the .Move method to go to the correct record in the recordset to get my date. And that is it!

Use
To use this function, you pass the start date to which you want to add x number of days (dteStart), the number of days that you want to add (intInterval) and then the days that you want to exclude (lngExcludePattern). The first two are pretty simple, but the Exclude Pattern needs more explanation. I have it setup to do it as binary flags. The first flag is Sunday and the last flag is Saturday. So if I want my weekends to be Saturday and Sunday, my Exclude Pattern would be 1000001. If I want Wednesdays excluded as well, then my pattern would be 1001001. If I just want Saturdays excluded it would be 0000001, which can also be entered as 1 because it is passed as a long integer. So if I want to add 5 days to 1/17/16, excluding Saturdays and Sundays, I would call my function like this:
Expand|Select|Wrap|Line Numbers
  1. =AddDays(#1/17/16#, 5, 1000001)

Conclusion
There are probably other ways to do this, but this seemed like the most versatile method and it actually does what I want it to. If anyone has any critiques, I welcome them.
Jan 15 '16 #1
4 6175
Narender Sagar
189 100+
Hi
It may be a silly question.
But I'm not able to figure out, how to use this final code?
Jan 16 '16 #2
Seth Schrock
2,965 Expert 2GB
It isn't a silly question, but a oversight on my part. I have added a Use section to my original post. Does that provide enough of an answer for you, or do I need to explain in more detail?
Jan 17 '16 #3
Hello,

that's very kind of you sharing this code. I am a novice user but manage to implement it, but...here, from Catalonia (European dates as dd/mm/yyyy), the codes works fine while the date matches my Access installation (dd/mm/yyyy). I mean that Access decides that 13 cannot be a month and treats this 13/01/2016 as properly (for me) whereas below 13, any date in the same list is treated as mm/dd/yyyy and the AddDays function add the interval to months instead of days.
Please, could you give some clues on how to fix it?
Thank you very much!!!
Jul 28 '16 #4
zmbd
5,501 Expert Mod 4TB
jordiseco: Access works internally with the #dd/mm/yyyy# format - read more about that here:
+ Allen Browne - International Dates in Access
and here
+ Bytes.Com - Literal DateTimes and Their Delimiters (#)

Often Access will correctly recognize the local regional date format; however, to ensure things are correctly interpreted internally one should use the "#" and the mm/dd/yyyy formatting for VBA and SQL.

:)
Aug 2 '16 #5

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

Similar topics

1
by: igendreau | last post by:
I have users inputting a "Request Date". Upon entering a date, I need Access to populate a second field ("Due Date"). When they enter their Request Date, I want Access to set the default value of...
4
by: CDMAPoster | last post by:
I'm starting to come up with a version of DateAdd that I call BusinessDateAdd that adds the selected number of business days. It's still in preliminary form (needs testing) and interacts with my...
7
by: Sam | last post by:
Hi, I use C# in my ASP.NET projects. Here's what I need to do: I want to add x business days to a given date i.e. add 12 business days to today's date. What is the best, fastest and most...
8
by: =?Utf-8?B?QWw=?= | last post by:
I am working in vb2005. how can I calculate business days (not including holidays and weekends) between 2 dates? thanks Al
1
by: ArchMichael | last post by:
i need help again on calculating business days excluding holidays i have a field called assign date and i need to calculate 7 business days excluding holidays ( already have a table for holiday)...
7
by: Mike | last post by:
I have a routine that's calculating business days but its not counting the weekend days that are between the start date and end date. If my start date is 9/26/08 and my end date is 10/01/08, I...
1
by: almurph | last post by:
Hi, I have a trickly little problem that I hope you can help me with. I need to add *working* days to a DateTime object of the format: yyyy/MM/ dd For example: say I have a timestamp of the...
5
FishVal
by: FishVal | last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL. So, let us say the problem is to calculate...
0
by: uno7031 | last post by:
Help Please!!! Adding 5 Days to another Date in an access query Good Morning, Help please…. I am new to access and trying to write a query that will add 5 days between a RecDate and a...
7
by: Fannie Dlamini | last post by:
hi guys am creating a leave system in an access form,i want it to add automatically two days to employees every month(thats if the employee has not taken days),for example if today's date was the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.