Dates in UDF Function Help needed

[Shamsuddeen]

Hello everybody,

I have a UDF with three parameters as shown below to calculate the end of service benefits of employees:

=BENEFIT(Salary,JoinDate,EndDate)

While using the above UDF, it accepts the Dates only in the format of
DATE(year,month,day) as in =BENEFIT(7500.25,Date(2002,10,25),Date(1998,8,17))

I would like to know how to use the UDF in the following format; without the word DATE or " " marks.

=BENEFIT(7500.25, 25-10-2002, 17-8-2002)

Help is greatly appreciated.

 

[Legacy 98055]

Why?
If you are going to pass 25-10-2002 without quotes, then your are sending 25 minus 10 minus 2002 or -1987 to your function. Why not pass a range? What are you doing exactly?
Tom

 

[Jack in the UK]

Nice feed very intresting care to post the whole code you have so far?

And the new custom function you would use!

Cheers
Jack

 

[Shamsuddeen]

Please find below the complete code. This UDF for calculating the end of service benefit of employees as explained by the Function itself.


'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, Hdate As Date, Edate As Date)

'Calculate the length of service in Days

Service = WorksheetFunction.Days360(Hdate, Edate) + 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
above5 = "Yes"
Else
above5 = "No"
End If

If above5 = "No" Then
BENEFIT1 = ((years * 12 + months) / 24) * Salary + (days / 720) * Salary
Else
BENEFIT1 = (12 * 5 / 24) * Salary
End If

If above5 = "No" Then
BENEFIT2 = 0
Else
BENEFIT2 = (((years - 5) * 12 + months) / 12) * Salary + (days / 360) * Salary
End If

BENEFIT = (BENEFIT1 + BENEFIT2)

End Function
**********************
The above works fine in the following three cases :
1. =BEBEFIT(7500,DATE(1995,12,20),DATE(2002,10,30))

2. =BENEFIT(7500,"12/20/1995","10/30/2002")

3. =BEBEFIT(7500,A1,A2)

I would like to know how the modify the above UDF to accept the following:

=BENEFIT(7500,20121995,30102002)

Expecting a solution.

Shamsuddeen

 

[Jay Petrulis]

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>

 

[Shamsuddeen]

Hi Jay Petrulis,

Wonderful post. It's working perfectly alright.

Could you please try to modify the codes to accept the Sdate and Edate from a range as in =BENEFIT(7500,A1,A2) where A1 and A2 are formatted as dates.

If the cells A1 and A2 are formatted as text as "25012002" the UDF returns the right answer but if they are input as dates it gives an error.

Regards,

Shamsuddeen

 

[Jay Petrulis]

Sorry, I did not see your follow-up.

Try the following...

<pre>Function BENEFIT(Salary As Double, Hdate, Edate) 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


If IsDate(Hdate) Then
HireDate = Hdate
Else
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)
End If

If IsDate(Edate) Then
EndDate = Edate
Else
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)
End If

Service = WorksheetFunction.Days360(HireDate, EndDate) + 1

years = Int(Service / 360)
months = Int(Service / 30) - years * 12
days = Service - (years * 360 + months * 30)

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>

 

[Shamsuddeen]

Wonderful Post !!!!!
Thank you very much.

Shamsuddeen