Hi,
I rewrote your UDF a bit to handle the dates as you wish. I did not adjust your formulas, as I assume that you have them working as you want.
I did, however, get rid of the "above5" test and instead directly assigned benefit1 and benefit2 depending on the length of service.
Please report your results.
<pre>Option Explicit
'UDF to Calculate Employee's End of Service Benefit
'Parameters :
' Salary : Gross Salary
' Hdate : Date onwhich the employee was hired
' Edate : Date onwhich the service was ended
Function BENEFIT(Salary As Double, Hdate As Long, Edate As Long) As Double
Dim Service As Long
Dim hyr As Integer, hmnth As Integer, hday As Integer
Dim eyr As Integer, emnth As Integer, eday As Integer
Dim HireDate As Date, EndDate As Date
Dim years As Integer, months As Integer, days As Integer
Dim above5, Benefit1, Benefit2
hyr = Right(Hdate, 4)
hmnth = Mid(CStr(Hdate), 3 + Len(CStr(Hdate)) - 8, 2) + 0
hday = Mid(CStr(Hdate), 1, 1 + Len(CStr(Hdate)) - 7) + 0
HireDate = DateSerial(hyr, hmnth, hday)
eyr = Right(Edate, 4)
emnth = Mid(CStr(Edate), 3 + Len(CStr(Edate)) - 8, 2) + 0
eday = Mid(CStr(Edate), 1, 1 + Len(CStr(Edate)) - 7) + 0
EndDate = DateSerial(eyr, emnth, eday)
'Calculate the length of service in Days
Service = WorksheetFunction.Days360(HireDate, EndDate) + 1
'Seperate the Service in Years, Months and Days
years = Int(Service / 360)
months = Int(Service / 30) - years * 12
days = Service - (years * 360 + months * 30)
'If the Service <= 5 years then the Benefit is 15 Days Salary
'for each of the years. (i.e. 2.5 months' salary)
'If the Service > 5 years then the Benefit is 15 Days Salary
'for each of the first 5 years and one month salary for the each
'of the subsequent years.
'to check if the services is above 5 years
If (Service / 360) > 5 Then
Benefit1 = (12 * 5 / 24) * Salary
Benefit2 = (((years - 5) * 12 + months) / 12) * Salary + (days / 360) * Salary
Else
Benefit1 = ((years * 12 + months) / 24) * Salary + (days / 720) * Salary
Benefit2 = 0
End If
BENEFIT = (Benefit1 + Benefit2)
End Function</pre>