Date Calculation (Working Days Minus Holidays)

[tnago]

I am trying to calculate number of day since a particular event. A user enters a date on which a an e-mail is received. Once that date has been entered, I like to automatically calculate the number of working days including holidays (which are already defined in another table).

So basically it is comparison of two dates and then calculating number of workings and displaying them on the form. (The first date is populated when an event occurs and second date is entered when an e-mail notification regarding the event is received). The default value is zero.

Any help in this regard will be greatly appreciated.

 

[ZmrAbdulla]

Hi,
Check this faq705-3213
It may help you.

ZmrAbdulla

 

[tnago]

Hi

I saw another FAQ (

http://www.tek-tips.com/faqs.cfm?spid=181&sfid=261)

from the one suggested by ZmrAbdulla. This particular FAQ covers all the condition which I am looking for to do the calculation. However, being new to Access I have problem as to where to put the code. What I can gather that there is need to create a table with all the holidays which needs to be taken into consideration. Maybe a module needs to be created for the excecution of the code. But I am not sure how this code will be executed? Can anyone please throw some light on this or point me to an example where this code or similar code is being used?

 

[MichaelRed]

TRhe CODE goes intp a 'general' module and is "called" with the tow date and the return is assigned to the variable (or control) to display the difference.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PassingBy]

Here are some functions which I found (can't remember where so can't accredit it - sorry) but I have adapted it to suit my needs and found them very useful.

Store these in a module and call then when you wish to check any given date or range of dates.

'Calculates Easter Sunday
Public Function EasterDate() As Date
Dim Yr As Integer
Yr = datepart("yyyy", Date)
Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + d + (d > 48) + 1) Mod 7)
End Function

Public Function EasterFriday() As Date
Dim Yr As Integer
Yr = datepart("yyyy", Date)
Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterFriday = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + d + (d > 48) + 1) Mod 7) - 2
End Function

Public Function EasterMonday() As Date
Dim Yr As Integer
Yr = datepart("yyyy", Date)
Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterMonday = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + d + (d > 48) + 1) Mod 7) + 1
End Function

Public Function KnownDay(Y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
'eg. For 1st Monday in specific month
KnownDay = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
End Function

Public Function KnownDay1(Y As Integer, M As Integer, DOW As Integer) As Integer
Dim I As Integer, Lim As Integer
Lim = Day(DateSerial(Y, M + 1, 0))
KnownDay1 = 0
For I = 1 To Lim
If WeekDay(DateSerial(Y, M, I)) = DOW Then
KnownDay1 = KnownDay1 + 1
End If
Next I
Exit Function
KnownDay1 = 0
End Function


Sorry I cannot tell you how these work but they do!

Well done to whoever coded it

PassingBy