Custom Function

Posted by Shamsuddeen. P.K. P.K on December 12, 2001 3:54 AM

I have created the following function which calculates the number of days between two given dates:

Function TotDays(EndDate,StartDate)
TotDays = EndDate - StartDate
End Function

The above function is working fine but te problem is that every time I use the function, I have to give the EndDate and StartDate in either of the following formats:

=TotDays(DATE(2001,12,31), DATE(2001,6,15) or

=TotDays(DATEVALUE(12-31-2001"),DATEVALUE("6-15-2001"))

In the above function, I want to give the dates in a simplified way such as :

=TotDays(31-12-2001, 15-6-2001) and the function should convert the dates to the required format and calculate the number of days.

Any help !!!!

Regards,

Shamsuddeen. P.K

Posted by Mike C on December 12, 2001 4:19 AM

How about this:

Public Function TotDays( _
EndDate As Date, _
StartDate As Date _
) As Integer
TotDays = EndDate - StartDate
End Function

then you can use the following formulas:

=TotDays("12-31-2001","6-15-2001")
=TotDays(A1,B1) where each cell contains a date

The string you pass can be any date format that can be converted by VB,e.g. Dec 1, 2001

The code will obey the month-day ordering defined by your regional settings.