# Dates in UDF Function Help needed

#### Shamsuddeen

##### Active Member
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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

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

And the new custom function you would use!

Cheers
Jack

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

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.

<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>

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

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>

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

Shamsuddeen

Replies
0
Views
466
Replies
0
Views
670
Replies
7
Views
822
Replies
1
Views
434
Replies
0
Views
481

1,219,800
Messages
6,150,322
Members
450,951
Latest member
kh198

### 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.

### Which adblocker are you using?

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

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