HELP !!! MS-Access date calculations on 5 day workweek...

  • Thread starter Thread starter michael.staschull
  • Start date Start date
M

michael.staschull

Hi,

I am fairly new to VBA programming.... I need help with a function
which calculates days based on a 5 day work calendar. The cut off time
is Friday 07:00 PM EST to Sunday 07:00 PM EST.

Example:

A ticket gets dispatched on a Friday 05:00 PM EST and I have 48 hrs to
respond. The calculation should be able to take the cut off time into
consideration which means it should give me a latest response date/
time of Tuesday 05:00 PM EST. (Friday from 05:00 PM to 07:00 PM = 2
hrs. and then from Sunday 07:00 PM EST +46 hrs. = Tuesday 05:00 PM).

Same goes for Resolution time/time the ticket is on-hold....

Can someone help ???

Thanks,
Michael
 
Michael,

Give the following a try. Place the following public function in a standard
module.

Public Function CalcCutOffDate(StartDate As Date) As Date
Dim CutOffDate As Date
Dim DayOfWeek As Integer
CutOffDate = DateAdd("h", 48, StartDate)
DayOfWeek = Weekday(CutOffDate)
If Weekday(CutOffDate) = 7 Then
CutOffDate = DateAdd("d", 2, CutOffDate)
ElseIf Weekday(CutOffDate) = 1 Then
CutOffDate = DateAdd("d", 1, CutOffDate)
End If
CalcCutOffDate = CutOffDate
End Function

Then call the function from the After_Update event of the dispatch date with
the following statement:

Me.txtCutoffDate = CalcCutOffDate(Me.txtStartDate)

"txtCutoffDate" is the name of the cutoff date control and the
"txtStartDate" is the dispatch date.
 
Michael,

Give the following a try. Place the following public function in a standard
module.

Public Function CalcCutOffDate(StartDate As Date) As Date
Dim CutOffDate As Date
Dim DayOfWeek As Integer
CutOffDate = DateAdd("h", 48, StartDate)
DayOfWeek = Weekday(CutOffDate)
If Weekday(CutOffDate) = 7 Then
CutOffDate = DateAdd("d", 2, CutOffDate)
ElseIf Weekday(CutOffDate) = 1 Then
CutOffDate = DateAdd("d", 1, CutOffDate)
End If
CalcCutOffDate = CutOffDate
End Function

Then call the function from the After_Update event of the dispatch date with
the following statement:

Me.txtCutoffDate = CalcCutOffDate(Me.txtStartDate)

"txtCutoffDate" is the name of the cutoff date control and the
"txtStartDate" is the dispatch date.

--
HTH

Mr B









- Show quoted text -
Click to expand...

Hi Mr. B

I somehow cannot get the After_Update to work..... Can I send you my
sample database ???

Thanks and best regards,
Michael
 
michael,

If you want to send me a copy of the database, I will take a look.

Send to: (e-mail address removed)

Just remove the nospamplease.
 
Back
Top