473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

add business days, workday calculations

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
17 15366
Hi

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

=DateAdd("d",[YourDateControlName],[YourBusinessDaysControl])

Hope it helps (and makes sense)

Regards
Emil
Oct 23 '08 #2
NeoPa
32,556 Expert Mod 16PB
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
youmike
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
NeoPa
32,556 Expert Mod 16PB
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
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
  4.  
  5. Function GetTheDate()
  6.  
  7. Dim db As Database, rs As Recordset, sSQL, DW As String
  8. Dim loopend, ct, x As Integer
  9. 'Dim sENum As String
  10.  
  11.     Set db = CurrentDb
  12.     DueDate = Trim(Format(DueDate, "m/d/yyyy"))
  13.     sSQL = "SELECT * FROM [Holiday Table] WHERE [HolidayDate] = '" & DueDate & "'"
  14.  
  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
  22.  
  23.     rs.close
  24.     db.close
  25.  
  26.  
  27. End Function
  28.  
  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
  37.  
  38.    Me.Priority_level_new = "Standard"
  39.    If Me.Priority_level_new = "Standard" Then
  40.         Me.Days = "1"
  41.    End If
  42.  
  43.  
  44.  
  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
  56.  
  57.             End If
  58.         End If
  59.     End If
  60.  
  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
  76.  
  77. End Sub
Nov 24 '08 #6
NeoPa
32,556 Expert Mod 16PB
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
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
NeoPa
32,556 Expert Mod 16PB
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
FishVal
2,653 Expert 2GB
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>
    2.  
  • 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.

Regards,
Fish

P.S. When HowTo section will be alive I will post an article there including db sample.
Nov 25 '08 #10
NeoPa
32,556 Expert Mod 16PB
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
tdw
206 100+
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
NeoPa
32,556 Expert Mod 16PB
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
tdw
206 100+
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
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
FishVal
2,653 Expert 2GB
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. :(

Regards,
Fish
Nov 27 '08 #16
NeoPa
32,556 Expert Mod 16PB
@trixxnixon
Probably over half of mine is. Otherwise I do network management and other IT related tasks.
Nov 27 '08 #17
@tdw
HA!!
wallmart
my cell phone can also do it.
Dec 1 '08 #18

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

Similar topics

1
by: David Stockwell | last post by:
I''m wondering if the Calendar object has an option to only do calculations based on business days (ie M-F). Additionally does it have a way to get holidays into itself? Currently I'm...
12
by: Anthony Robinson | last post by:
Is anyone aware of a function (system or user defined) that will calculate business days? For instance: I have a column in as table called DATE. I want to be able to add five business days to that...
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)...
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...
3
by: PotatoChip | last post by:
I'm working in an Access XP database and I need to create a query which calculates what the date will be 6 business days after . I have no idea where to start and most posts I find on calculating...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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: 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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.