Calculating dates with breaks

  • Thread starter Thread starter Sabram
  • Start date Start date
S

Sabram

Hi

I have been asked to create a database for UK Freedom of Information (FoI)
requests which when complete I will share with whoever wants it. My problem,
(or one of many!) is how to calculate dates on a form.

In FoI we have 20 working days to complete a request, I can calculate the
date from date_received to 20 days with "completion_date = date_received +
20" but how do I get it to do working days?

It gets even more complex, for me at least, when we can have breaks for
example;

Jan 1st 2005 request comes in - completion date = 28th Jan 2005 (20 working
days)
Jan 7th we request clarification - clock stopped with 15 days to go
Jan 13th clarification received, clock resumes with a completion date now of
Feb 1st
Jan 20th we request the fee, clock stops with 9 days to go
Feb 1st we get the fee and now have until Feb 11th to complete the request

Can anyone give me a clue on this?

As I said once complete I am willing to provide it to anyone who can make
use of it...

Thanks

Steve
 
Sabram,

Here's a function for workdays that should be helpful. Copy and paste the
following into an access module. You can test it in the immediate window by
typing: ? DateAddW(#1/1/05#,20)

Good luck,

Ken

'**********************************************************
'Declarations section of the module
'**********************************************************

Option Explicit

'==========================================================
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'==========================================================
Function DateAddW(ByVal TheDate, ByVal Interval)

Dim Weeks As Long, OddDays As Long, Temp As String

If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
VarType(Interval) > 5 Then
DateAddW = TheDate
ElseIf Interval = 0 Then
DateAddW = TheDate
ElseIf Interval > 0 Then
Interval = Int(Interval)

' Make sure TheDate is a workday (round down).

Temp = Format(TheDate, "ddd")
If Temp = "Sun" Then
TheDate = TheDate - 2
ElseIf Temp = "Sat" Then
TheDate = TheDate - 1
End If

' Calculate Weeks and OddDays.

Weeks = Int(Interval / 5)
OddDays = Interval - (Weeks * 5)
TheDate = TheDate + (Weeks * 7)

' Take OddDays weekend into account.

If (DatePart("w", TheDate) + OddDays) > 6 Then
TheDate = TheDate + OddDays + 2
Else
TheDate = TheDate + OddDays
End If

DateAddW = TheDate
Else ' Interval is < 0
Interval = Int(-Interval) ' Make positive & subtract later.

' Make sure TheDate is a workday (round up).

Temp = Format(TheDate, "ddd")
If Temp = "Sun" Then
TheDate = TheDate + 1
ElseIf Temp = "Sat" Then
TheDate = TheDate + 2
End If

' Calculate Weeks and OddDays.

Weeks = Int(Interval / 5)
OddDays = Interval - (Weeks * 5)
TheDate = TheDate - (Weeks * 7)

' Take OddDays weekend into account.

If (DatePart("w", TheDate) - OddDays) > 2 Then
TheDate = TheDate - OddDays - 2
Else
TheDate = TheDate - OddDays
End If

DateAddW = TheDate
End If

End Function
 
You will also need to add a "Holiday" table so you can exclude those dates.
You can check if there is a holiday during the projected period with DCount.
 
Back
Top