Hi All,
I made a function to calculate the difference in hours between two times.
I have a query where there are 2 sets of times in fields (Start1, Finish1, Start2, Finish2).
When I add a calculated column with the following expression:
it calculates in the query correctly.
However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field.
Does anyone know why this would #Error. I have the TimeDiff function initialising to = 0 before doing anything...I am stumped why it returns #Error when it should return 0 in the case of something going wrong.
Brad
I made a function to calculate the difference in hours between two times.
Code:
Function TimeDiff(Time1 As String, Time2 As String) As Double
'This function is used to calculate the difference in hours between two times.
'It is capable of handling a finish time that is after midnight.
TimeDiff = 0
If Not IsNull(Time1) And Not IsNull(Time2) Then
If CDate(Time1) > CDate(Time2) Then
TimeDiff = ((CDate(Time2) - CDate(Time1)) + 1) * 24
Else
TimeDiff = (CDate(Time2) - CDate(Time1)) * 24
End If
End If
End Function
When I add a calculated column with the following expression:
Code:
Expr1: TimeDiff(Start1,Finish1) + TimeDiff(Start2,Finish2)
However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field.
Code:
Function OrdinaryHours(Status As String, Start1 As String, Finish1 As String, Optional Start2 As String, Optional Finish2 As String) As Double
'Initialise
OrdinaryHours = 0
'If employee is NOT a casual
If Not Status = "CT" Then
If Not IsMissing(Start2) And Not IsMissing(Finish2) Then
OrdinaryHours = TimeDiff(Start1, Finish1) + TimeDiff(Start2, Finish2)
Else
OrdinaryHours = TimeDiff(Start1, Finish1)
End If
End If
End Function
Brad