Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

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


Re: Problem with Function using Dates

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

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

Jacob


Re: Problem with Function using Dates

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.


Re: Problem with Function using Dates

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


Problem with Function using Dates - Detailed Problem

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


Re: Problem with Function using Dates - Detailed Problem

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


Re: Problem with Function using Dates - Complete Codes

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



Re: Problem with Function using Dates - Complete Codes

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.