fragglemoo
IS-IT--Management
I'm using Crystal 2008 and have been given this VB formula to do some time calculations. I need to get it to work in CR, can anyone help me? I'm totally lost 
Thanks.
Function WorkingHours(startDateTime As Variant, endDateTime As Variant)
Const FIVE_THIRTY_PM = 17.5 / 24#
Const NINE_AM = 9# / 24#
Dim startDate As Variant, startTime As Variant, endDate As Variant, endTime As Variant, _
totalTime As Variant, days As Integer
If Trim$(startDateTime) = "" Or Trim$(endDateTime) = "" Then
WorkingHours = ""
Exit Function
End If
startDate = CDate(Split(startDateTime, " ")(0))
startTime = Split(startDateTime, " ")(1)
startTime = (CInt(Split(startTime, ":")(0)) + CInt(Split(startTime, ":")(1)) / 60) / 24
endDate = CDate(Split(endDateTime, " ")(0))
endTime = Split(endDateTime, " ")(1)
endTime = (CInt(Split(endTime, ":")(0)) + CInt(Split(endTime, ":")(1)) / 60) / 24
totalTime = 0#
If startTime < NINE_AM Then
startTime = NINE_AM
End If
If endTime > FIVE_THIRTY_PM Then
endDate = NextWorkingDay(endDate)
endTime = NINE_AM + endTime - FIVE_THIRTY_PM
End If
If startDate = endDate Then
WorkingHours = (endTime - startTime)
Exit Function
End If
If startTime <= FIVE_THIRTY_PM Then
totalTime = FIVE_THIRTY_PM - startTime
End If
startDate = NextWorkingDay(startDate)
totalTime = totalTime + endTime - NINE_AM
days = 0
Do While startDate < endDate
startDate = startDate + 1
If Weekday(startDate, vbMonday) <= 5 Then
days = days + 1
End If
Loop 'Until startDate >= endDate
WorkingHours = totalTime + days * 8.5 / 24
End Function
Private Function NextWorkingDay(current As Variant) As Date
Dim dOW As Integer
current = current + 1
dOW = Weekday(current, vbMonday)
If dOW > 5 Then
current = current + 8 - dOW
End If
NextWorkingDay = current
End Function
Thanks.
Function WorkingHours(startDateTime As Variant, endDateTime As Variant)
Const FIVE_THIRTY_PM = 17.5 / 24#
Const NINE_AM = 9# / 24#
Dim startDate As Variant, startTime As Variant, endDate As Variant, endTime As Variant, _
totalTime As Variant, days As Integer
If Trim$(startDateTime) = "" Or Trim$(endDateTime) = "" Then
WorkingHours = ""
Exit Function
End If
startDate = CDate(Split(startDateTime, " ")(0))
startTime = Split(startDateTime, " ")(1)
startTime = (CInt(Split(startTime, ":")(0)) + CInt(Split(startTime, ":")(1)) / 60) / 24
endDate = CDate(Split(endDateTime, " ")(0))
endTime = Split(endDateTime, " ")(1)
endTime = (CInt(Split(endTime, ":")(0)) + CInt(Split(endTime, ":")(1)) / 60) / 24
totalTime = 0#
If startTime < NINE_AM Then
startTime = NINE_AM
End If
If endTime > FIVE_THIRTY_PM Then
endDate = NextWorkingDay(endDate)
endTime = NINE_AM + endTime - FIVE_THIRTY_PM
End If
If startDate = endDate Then
WorkingHours = (endTime - startTime)
Exit Function
End If
If startTime <= FIVE_THIRTY_PM Then
totalTime = FIVE_THIRTY_PM - startTime
End If
startDate = NextWorkingDay(startDate)
totalTime = totalTime + endTime - NINE_AM
days = 0
Do While startDate < endDate
startDate = startDate + 1
If Weekday(startDate, vbMonday) <= 5 Then
days = days + 1
End If
Loop 'Until startDate >= endDate
WorkingHours = totalTime + days * 8.5 / 24
End Function
Private Function NextWorkingDay(current As Variant) As Date
Dim dOW As Integer
current = current + 1
dOW = Weekday(current, vbMonday)
If dOW > 5 Then
current = current + 8 - dOW
End If
NextWorkingDay = current
End Function
