add business days, workday calculations

[trixxnixon]

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.

[emsik1001]

Hi

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

=DateAdd("d",[YourDateControl Name],[YourBusinessDay sControl])

Hope it helps (and makes sense)

Regards
Emil

[NeoPa]

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.

'MoveWD moves datThis on by the intInc weekdays.
Public Function MoveWD(datThis As Date, _
                       intInc As Integer) As Date
    MoveWD = datThis
    For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
        MoveWD = MoveWD + Sgn(intInc)
        Do While (WeekDay(MoveWD) Mod 7) < 2
            MoveWD = MoveWD + Sgn(intInc)
        Loop
    Next intInc
End Function

[youmike]

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.

[NeoPa]

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.

[trixxnixon]

Thank you all!

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.

Option Compare Database
Option Explicit
Public DueDate As String

Function GetTheDate()

Dim db As Database, rs As Recordset, sSQL, DW As String
Dim loopend, ct, x As Integer
'Dim sENum As String
  
    Set db = CurrentDb
    DueDate = Trim(Format(DueDate, "m/d/yyyy"))
    sSQL = "SELECT * FROM [Holiday Table] WHERE [HolidayDate] = '" & DueDate & "'"
  
    Set rs = db.OpenRecordset(sSQL)
    If Not rs.EOF Then
        DW = rs![HolidayDate]
        GetTheDate = 1
    Else
       GetTheDate = 0
    End If
         
    rs.close
    db.close
  
    
End Function

Private Sub Date_Submitted_LostFocus()
Dim db As Database
    Dim rs As Recordset
    Dim sSQL, Dept, TN As String
    Dim vaData As Variant
    Dim k As Long
    Dim d, e, x, wct, dct
    Dim loopend, MyHour, colonPos
            
   Me.Priority_level_new = "Standard"
   If Me.Priority_level_new = "Standard" Then
        Me.Days = "1"
   End If
   

 
'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
    loopend = Val(Me.Days) 'default # of days
    DueDate = Me.Date_Submitted
    If Right(Me.Date_Submitted, 2) = "PM" Then
        colonPos = InStr(Me.Date_Submitted, ":")
        'If (Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) > 4) And (Trim(Format(Date, "dddd")) = "Friday") Then 'get the day of the week
        '    loopend = loopend + 1
            If Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) > 4 Then
                 loopend = loopend + 1
                 If Val(Mid(Me.Date_Submitted, colonPos - 2, 2)) = 12 Then
                     loopend = loopend - 1
                
            End If
        End If
    End If
    
    'add a day one at a time and check to see if a weekend falls inbetween
    dct = 0
    Do
        d = Trim(Format(DueDate, "dddd")) 'get the day of the week
        'check for a holiday and a weekend day
        If d = "Saturday" Or d = "Sunday" Or GetTheDate = 1 Then
            DueDate = DateAdd("w", 1, DueDate)
        ElseIf dct = loopend Then 'reached number of business due days
            Exit Do
        Else 'weekday
            dct = dct + 1
            DueDate = DateAdd("w", 1, DueDate)
        End If
    Loop
    Me.Due_Date = DueDate

End Sub

[NeoPa]

Seems like some good progress there Trixx :)

The basic concepts are good and very much in line with the suggestions given.

[trixxnixon]

i had some in house help with the date calculations as well.... dont want to take credit where credit is not due.

[NeoPa]

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

[FishVal]

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.
  

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

Regards,
Fish

P.S. When HowTo section will be alive I will post an article there including db sample.

[NeoPa]

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.

[tdw]

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.

[NeoPa]

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.

[tdw]

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

[trixxnixon]

hey

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?