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

add business days, workday calculations

P: 98
i have a form with these fields

Priority level: urgent critical standard

business days: 1, 3, 15

date submitted: current date

due date:

the due date will calculate based on how many business days are returned which is based on priority level, for example 15 days passed 10/22/08 would return a date of 11/12/08.

i have no idea what i am doing, if anyone can help i would greatly appreciate it.
i would do almost anything to get this done asap.

Thank you in advance.
Oct 23 '08 #1
Share this Question
Share on Google+
17 Replies

P: 93

If you have business days already on the form then just add a text box control and type


Hope it helps (and makes sense)

Oct 23 '08 #2

Expert Mod 15k+
P: 31,310
Business days are tricky (trixxie). Weekdays are algorithmic, but business days must, by definition, be based on known data.

DateAdd("w", 5,Date()) should give the date one week hence. Unfortunately, at least up to version 2003, this simply adds days.

If you're interested, I have a routine to handle this though. Be sure to understand this only deals in week days, NOT business days.
Expand|Select|Wrap|Line Numbers
  1. 'MoveWD moves datThis on by the intInc weekdays.
  2. Public Function MoveWD(datThis As Date, _
  3.                        intInc As Integer) As Date
  4.     MoveWD = datThis
  5.     For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
  6.         MoveWD = MoveWD + Sgn(intInc)
  7.         Do While (WeekDay(MoveWD) Mod 7) < 2
  8.             MoveWD = MoveWD + Sgn(intInc)
  9.         Loop
  10.     Next intInc
  11. End Function
Oct 23 '08 #3

P: 69
I endorse what NeoPa says: there really is no practical alternative to creating a table of days with a flag set appropriately for each day. Another factor that you need to bear in mind is that holidays are not universal. Most are country specific and some are even region specific within a country.

Another thought: if you ever need to deal in daily working hours which vary according to the day of the week, or accounting periods which are not month based, you really have no choice but to go the route I suggest, with extra fields on the day table to manage all the other complications.

This approach may seem a lot of work, but my experience is that in the long run it's really the only method that works.
Oct 24 '08 #4

Expert Mod 15k+
P: 31,310
An alternative is to use a table of holidays rather than a table of all dates within a range.

A complication with any way of doing this of course, is that it's quite impractical to populate this table too far into the future (as many holidays are not entirely predictable). This approach must involve regular (if infrequent) maintenance of the data.

If this sounds like something you're prepared to do, then go for it. Otherwise, try your hardest to get acceptance for a simple weekday based procedure.
Oct 24 '08 #5

P: 98
Thank you all for your help and advice. here is what i used and it works well and is easily addapted to some of the crazier forms. i did not build this databse but sorta got excited and took a project that i was not skilled enough take. but i have learned so much.

there is a holiday table that will be populated with the days that we are closed based on the 2009 calendar.

here is what i am using, feel free to comment. any opinion you would like to share would just be more knowledge that i gain as i am still a novice.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Public DueDate As String
  5. Function GetTheDate()
  7. Dim db As Database, rs As Recordset, sSQL, DW As String
  8. Dim loopend, ct, x As Integer
  9. 'Dim sENum As String
  11.     Set db = CurrentDb
  12.     DueDate = Trim(Format(DueDate, "m/d/yyyy"))
  13.     sSQL = "SELECT * FROM [Holiday Table] WHERE [HolidayDate] = '" & DueDate & "'"
  15.     Set rs = db.OpenRecordset(sSQL)
  16.     If Not rs.EOF Then
  17.         DW = rs![HolidayDate]
  18.         GetTheDate = 1
  19.     Else
  20.        GetTheDate = 0
  21.     End If
  23.     rs.close
  24.     db.close
  27. End Function
  29. Private Sub Date_Submitted_LostFocus()
  30. Dim db As Database
  31.     Dim rs As Recordset
  32.     Dim sSQL, Dept, TN As String
  33.     Dim vaData As Variant
  34.     Dim k As Long
  35.     Dim d, e, x, wct, dct
  36.     Dim loopend, MyHour, colonPos
  38.    Me.Priority_level_new = "Standard"
  39.    If Me.Priority_level_new = "Standard" Then
  40.         Me.Days = "1"
  41.    End If
  45. 'if submitted after 4:59 PM then add a day if after 4:59 PM and a Friday add 2 days others wise add no extra days
  46.     loopend = Val(Me.Days) 'default # of days
  47.     DueDate = Me.Date_Submitted
  48.     If Right(Me.Date_Submitted, 2) = "PM" Then
  49.         colonPos = InStr(Me.Date_Submitted, ":")
  50.         'If (Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) > 4) And (Trim(Format(Date, "dddd")) = "Friday") Then 'get the day of the week
  51.         '    loopend = loopend + 1
  52.             If Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) > 4 Then
  53.                  loopend = loopend + 1
  54.                  If Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) = 12 Then
  55.                      loopend = loopend - 1
  57.             End If
  58.         End If
  59.     End If
  61.     'add a day one at a time and check to see if a weekend falls inbetween
  62.     dct = 0
  63.     Do
  64.         d = Trim(Format(DueDate, "dddd")) 'get the day of the week
  65.         'check for a holiday and a weekend day
  66.         If d = "Saturday" Or d = "Sunday" Or GetTheDate = 1 Then
  67.             DueDate = DateAdd("w", 1, DueDate)
  68.         ElseIf dct = loopend Then 'reached number of business due days
  69.             Exit Do
  70.         Else 'weekday
  71.             dct = dct + 1
  72.             DueDate = DateAdd("w", 1, DueDate)
  73.         End If
  74.     Loop
  75.     Me.Due_Date = DueDate
  77. End Sub
Nov 24 '08 #6

Expert Mod 15k+
P: 31,310
Seems like some good progress there Trixx :)

The basic concepts are good and very much in line with the suggestions given.
Nov 25 '08 #7

P: 98
i had some in house help with the date calculations as well.... dont want to take credit where credit is not due.
Nov 25 '08 #8

Expert Mod 15k+
P: 31,310
Fair enough.

But sometimes it's about who has the brights to ask for/get help.

Anyway, it's all progress, so allow yourself some congratulations (while thanking those people who helped).
Nov 25 '08 #9

Expert 2.5K+
P: 2,653
Hello, gentlemen.

Just to make everyone happy I would like to say that the problem has a pure SQL solution. The main features of the solution are the folowing:
  • Sequential dates list is being generated dynamically via cartesian join of 3 tables containig days (1 to 31), months (1 to 12) and years (list should cover expected range). Certain 31, 30 and 29 days are excluded using comparisson of DateSerial() result with day argument.
    Expand|Select|Wrap|Line Numbers
    1. .... WHERE Day(DateSerial(<d>, <m>, <y>))=<d>
  • Periods to calculate business dates from associated with contact are stored in table.
  • Different contacts are associated with different sets of days off.
  • Different contacts could be associated with different sets of holidays. Holidays having the same d/m each year could be stored as single record.
  • Flat dates list obtained via cartesian join is outer joined with periods date to filter dates falling within periods, then with days off associated with particular contacts to remove days off records, then with holidays.
  • Then aggregating query and, voila, business days count calculated.


P.S. When HowTo section will be alive I will post an article there including db sample.
Nov 25 '08 #10

Expert Mod 15k+
P: 31,310
An interesting concept Fish. I doubt too many of our ordinary members will want to get to grips with these levels of complexity, but options are always worth looking at (and I can think of a few of the staff who may be very interested in looking further into this).

When you have the article ready post a link in here so that we can have a look. A couple of thoughts spring to mind, but I will go there when you're more ready with it, and elsewhere than in this thread.
Nov 26 '08 #11

P: 206
Considering that I have an alarm clock from Walmart that knows to only go off on workday mornings and not weekends, it seems strange to me that Microsoft doesn't just incorporate something into the program that can do the same. Can't Outlook do this? I wonder if there would be a way to borrow Outlook's ability for this and use it in Access.
Nov 26 '08 #12

Expert Mod 15k+
P: 31,310
If you look earlier on in the thread John you'll notice that weekdays and weekends are fairly straightforward to include. What is more complex, is the complications of holidays of various sorts.

Some fall on the same day of the same month every year. Some fall on the last Monday of a month. Some fall on days that are only predictable by the leaders of your country. Some fall on days nominated by a random number generator (Ok - a little hyperbolae there :D). In short, though some fit into an algorithmic approach, some certainly do not seem to.

It is possible, with successively more complicated procedures, to cover more or less of these dates. Fundamentally though, as so many of them are hard to tie down at all, reliably predicting them in any way within a database will always be at least complicated, assuming it is even possible.
Nov 26 '08 #13

P: 206
Weather is even less predictable, and yet we can get the weather streamed in to our systrays. Microsoft really should have a similar set up for holidays, where the information is kept in sync via the internet, and able to be accessed or called from within Office programs.

...Or at least be able to manually flag holidays in the Outlook calendar and let Access read from that.

Sorry, I'm just thinking out loud (actually, silently...but with my fingers on the keyboard). Not particularly helpful in answering the poster's question. But it looked to me like the thread was about over anyway, until FishVal had an article ready :-)
Nov 26 '08 #14

P: 98
you guys are all awesome, this is by far my favorite forum to post and search in.
do you guys all have jobs that are access or database related?
Nov 26 '08 #15

Expert 2.5K+
P: 2,653
Gentlemen, I've posted the article on Access forum. HowTos are still down. Moreover, I couldn't attach sample db - maximum attachment size is set to 5k. :(

Nov 27 '08 #16

Expert Mod 15k+
P: 31,310
Probably over half of mine is. Otherwise I do network management and other IT related tasks.
Nov 27 '08 #17

P: 98
my cell phone can also do it.
Dec 1 '08 #18

Post your reply

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