Dates in UDF Function Help needed

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
L

Legacy 98055

Guest
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

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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

Active Member
Joined
Feb 16, 2002
Messages
292
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

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040

ADVERTISEMENT

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

Active Member
Joined
Feb 16, 2002
Messages
292
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

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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>
 

Forum statistics

Threads
1,143,617
Messages
5,719,740
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top