Problem with Function using Dates

Posted by Shamsuddeen. P.K on December 26, 2001 9:04 AM

I have the following custom Function to calculate the number of days between two dates :

Function DAYS(StartDate As Date, EndDate As Date)
Days = (EndDate - StartDate)
End Function

The function works fine and gives the deisred result, but the problem is that while using the Function I have to enclose the Dates in Quotations as shown below:

= DAYS("12-25-2001", "10-25-2001")

I would like to know how to modify the code so that the dates need to be given without the quotations ; =DAYS(12-25-2001, 10-25-2001)

Regards,

Shamsuddeen. P.K

Posted by Jacob on December 26, 2001 9:15 AM

There is a built in function that does this (dateif).

Jacob

Posted by Juan Pablo G. on December 26, 2001 12:17 PM

That's DATEDIF (Forgot the second D).

Also, why create a formula for that ? If start date is in A1 and end date is in A2, your formula would be

=A2-A1

No need for VBA for this.

Juan Pablo G.

Posted by Jack in UK on December 26, 2001 2:51 PM

Sorry cant you take one daye from the other?
Date B - Date A ??

Keep it simple
HTH
Jack

Posted by Shamsuddeen. P.K on December 27, 2001 9:49 AM

Sorry for not expalining my problem fully.

I have not given the complete codes for the Function I have written. The days calculated by the Function is used to calculate the end of service benefits of employees by taking into account the Salary and Service in years. The benefit gets doubled if the service is more than 5 years. I am using different types of calculations depends on the length of services which is arrived at by converting the days into years, months and days.

" Days = (EndDate - StartDate)

' codes for calculating end of service benefits
...............
...............
...............
..............

Posted by Jack in UK on December 27, 2001 2:27 PM

Ok sugest you post your code compleate, taht way we know where we stand and might be able to help

Posted by Shamsuddeen. P.K on December 29, 2001 9:20 AM

Function INDEM(Salary, Hdate As Date, Edate As Date)
service = WorksheetFunction.Days360(Hdate, Edate) + 1
years = Int(service / 360)
months = Int(service / 30) - years * 12
days = service - (years * 360 + months * 30)

If (service / 360) > 5 Then
above5 = "Yes"
Else
above5 = "No"
End If

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

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

INDEM = (indem1 + indem2)

End Function

----------------------------------------

While using the function, I have to enclose the Dates within " ".

Please let me know how to give the dates without " " as shown below :

=INDEM(5000,01-25-2001, 12-29-2001)

Your solution would be highly appreciated

Shamsuddeen

Posted by Jack in UK on December 30, 2001 10:10 AM

Hi Shamsuddeen--
Im lost i cant find a fault or error! I cant pin point why ecxel req " " for dtaes??? Odd. Im lost i cant find any treason seems you right, bizzar.

Try custom forat the date to dddd ddd mmmm yyyy and see it if behaves, else i need a look at the SS, can you email ?

Sorry im beaten here..

Let me know, stay to this feed. Thanks
Jack