megatronixs
Registered User.
- Local time
- Today, 13:52
- Joined
- Aug 17, 2012
- Messages
- 719
Code:
I have a function that check how many days passed since a request was started. It will stop whe the finalized date is entered. Now I need to make a change that when a new status is set, it should stop counting the days passed and only start counting days passed when another status is selected. If a request was started like 12 working days ago and the status is changed to "Awaiting Documentation" then, it should stop and only start counting again when the next status is changed to "Documents Received".
I added a new column to the database that I called "status_stop", that maybe could hold a date when the "Awaiting Documentation" was entered and that would then discount it from the passed days. But to be honest, I have no idea how to pull this one.
This is the code I have so far:
Code:
review_duration: CInt(ReviewDuration(Nz([tbl_main_data].[request_date],0),Nz([tbl_main_data].[finalized_date],0)))And these are the functions:
Code:
Option Explicit
Option Compare Database
Function DateDiffWorkDays(BegDate As Date, EndDate As Date) As Integer
'Returns number of days, excluding Saturday and Sunday
'As written, counts both BegDate and EndDate, e.g.,
'Monday - Friday would count as 5 days
'coded by: raskew
Const SATURDAY = 6
Const SUNDAY = 7
Dim NumWeeks As Integer
If BegDate = 0 Then
DateDiffWorkDays = 0
Exit Function
End If
Select Case Weekday(BegDate, vbMonday)
Case SATURDAY: BegDate = BegDate + 2
Case SUNDAY: BegDate = BegDate + 1
End Select
Select Case Weekday(EndDate, vbMonday)
Case SATURDAY: EndDate = EndDate - 1
Case SUNDAY: EndDate = EndDate - 2
End Select
If BegDate > EndDate Then
DateDiffWorkDays = 0
Exit Function
End If
NumWeeks = (EndDate - BegDate - ((EndDate - BegDate) Mod 7)) / 7
If Weekday(EndDate, vbMonday) >= Weekday(BegDate, vbMonday) Then
DateDiffWorkDays = NumWeeks * 5 - Weekday(BegDate, vbMonday) + Weekday(EndDate, vbMonday) + 1
Else
DateDiffWorkDays = NumWeeks * 5 + 5 - Weekday(BegDate, vbMonday) + Weekday(EndDate, vbMonday) + 1
End If
End Function
Function ReviewDuration(Optional Request As Date, Optional finalized As Date)
'TURNING finalized INTO TODAY'S DATE IF FINALIZED WAS MISSING
If finalized = 0 Then
finalized = Date
End If
'PRE-CONDITIONS
If Request = 0 Or _
Request > finalized Or _
Request > Date Then
ReviewDuration = 0
Exit Function
End If
'IF FINALIZED IS EMPTY (nz condition in query) THEN FINALIZED = TODAY'S DATE
If finalized = 0 Then
finalized = Date
End If
'CALCULATIONS
Dim intFullWeeksPassed As Integer
Dim intDaysCalculated As Integer
Dim intInsideWeekDifference As Integer
Select Case Weekday(Request, vbMonday)
Case 6
Request = Request + 2
Case 7
Request = Request + 1
End Select
Select Case Weekday(finalized, vbMonday)
Case 6
finalized = finalized - 1
Case 7
finalized = finalized - 2
End Select
'making check for situations, in which both dates were entered in the same weekend
If Request > finalized Then
ReviewDuration = 0
Exit Function
End If
'full weeks passed
intFullWeeksPassed = ((finalized - Request) - ((finalized - Request) Mod 7)) / 7
'calculation when finalized weekday >= request weekday or finalized weekday < request weekday
If Weekday(finalized, vbMonday) >= Weekday(Request, vbMonday) Then
intInsideWeekDifference = Weekday(finalized, vbMonday) - Weekday(Request, vbMonday)
Else
intInsideWeekDifference = 5 + Weekday(finalized, vbMonday) - Weekday(Request, vbMonday)
End If
' final calculation
ReviewDuration = intFullWeeksPassed * 5 + intInsideWeekDifference + 1
ReviewDuration = CInt(ReviewDuration)
Exit Function
ErrorHandler:
ReviewDuration = 0
End FunctionGreetings.
formula in the query:
