I'm using a module I found on the net(thanks to whomever the author is). I'm trying to use it referencing fields such as... ([START DATE],[TIME TO PROCESS]). Since my time to process is a field I'm pulling in from a table and can be unique for each record. However it only wants to work when a number is actually entered in the expression for the duration to add... such as...
([START DATE],10)
My table would look something like this.
START DATETIME TO PROCESS10/10/20141010/20/2014511/30/20142
Code Below from module:
'************************************************* *********
'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
'************** End of Code **************
Module Help!
([START DATE],10)
My table would look something like this.
START DATETIME TO PROCESS10/10/20141010/20/2014511/30/20142
Code Below from module:
'************************************************* *********
'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
'************** End of Code **************
0 commentaires:
Enregistrer un commentaire