Calculate number of days using 360 day basis

[Numbers1]

Excel has a function Days360 which is used to calculate the number of days between 2 dates as if each month had 30 days. It appears VBA does not have that function. DateDiff uses a 365 day calendar.
Does anyone know how to calculate on the basis of a 360 day calendar when finding the number of days between 2 dates?
I am using this in a formula in my code as follows:
PrincipalAmt x CouponRate x (Date1 - Date2) / 360 = AccruedInterest in the period

One suggestion on a previous post was to use the following:

Application.WorksheetFunction.Days360(Date1, Date2)

Date1 and Date2 populate but nothing else does. The difference between the 2 dates is not calculated. In checking the help files it appears that the Application. object is still an Excel object and not a VBA function. Can anyone else help?
Thanks, Numbers

 

[PHV]

Which VBA ?
What is your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Numbers1]

I am writing a macro in Excel to calculate the accrued interest on a list of bonds since their last interest payment. I want to be able to insert a new date to accrue to and the macro will calculate each time that I change the date. Some bonds are calculated on a 365 day basis and others on a 360 day basis. I load the basic information needed to make the calculation from the Excel worksheet and then perform the calculation for that bond. Below is the portion of code that does the actual calculation. I have not shown the DIM statements. The line that doesn't work is noted with ***

' Load data to perform calculation from present line
Worksheets("Accr Int").Cells(intRow, intCouponCol).Select
sngCoupon = ActiveCell
Worksheets("Accr Int").Cells(intRow, intDayBasisCol).Select
intDayBasis = ActiveCell
Worksheets("Accr Int").Cells(intRow, intLastIntPmtCol).Select
dteLastIntPmt = ActiveCell
Worksheets("Accr Int").Cells(intRow, intAccrIntCol).Select
curAccrInt = ActiveCell

' Choose day basis and calculate accrued interest
If intDayBasis = 365 Then
curAccrInt = curFaceAmt * sngCoupon * (dteAccrDate - dteLastIntPmt + 1) / 365
ActiveCell = curAccrInt
Else
*** dblNumberDays = Application.WorksheetFunction.Days360(dteLastIntPmt, dteAccrDate)
curAccrInt = curFaceAmt * sngCoupon * dblNumberDays / 360
ActiveCell = curAccrInt
End If

 

[PHV]

doesn't work
what happens ? error message ? computer crash ? unexpected result ? ... ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Numbers1]

No result! When I step through the macro the 2 dates populate but there is no result. dblNumberDays remains at a value of 0.
Numbers.

Thanks,
Numbers

 

[MJPPaba]

mydays = WorksheetFunction.Days360(DateValue("1 Jan 2007"), DateValue("5 Jan 2007"))

 

[MJPPaba]

the dates required are in julian format, so by putting the real date into the datevalue function it is then converted into the julian date format required.

Cheers

Matt

 

[Numbers1]

That doesn't work either. I tried entering the DateValue method as shown in the last response and any number of other combinations. Nothing passes the number of days out. The calculation is not happening.
Any other ideas?
Numbers

Thanks,
Numbers

 

[combo]

Your original solution should work. Verify cell references (intRow etc., you do not have dteAccrDate in the code above) and the way you keep dates in cells (should not be strings). Thest result directly:

MsgBox Application.WorksheetFunction.Days360(Worksheets("Accr Int").Range("A1"), Worksheets("Accr Int").Range("B1")

You can simplify code, instead:

Worksheets("Accr Int").Cells(intRow, intCouponCol).Select
sngCoupon = ActiveCell

use:

sngCoupon=Worksheets("Accr Int").Cells(intRow, intCouponCol)

combo

 

[Numbers1]

Found the problem. I had defined the variable as currency. The 360 day method only accepts variables defined as doubles. Appreciate everyones' input and help. You guys are great.
Numbers

Thanks,
Numbers