PDA

View Full Version : DAYS360 in VBA



ungermkm
01-04-2015, 03:55 PM
I'm trying to compare dates in VBA and then use days360 between them, but I can't get it to work. Any thoughts?


Dim percentDateA As Date
Dim percentDateB As Date
Dim percentDateC As Date
Dim percentDateD As Date
Dim elapsedTime As Double
Dim days360temp As Double

elapsedTime = 0
For x = 3 To 82
For w = 64 To 68
For y = 28 To 61 Step 2
percentDateA = Cells(x, y)
percentDateB = Cells(x, y + 2)
percentDateC = Cells(1, w)
percentDateD = Cells(2, w)

If Cells(x, y) < Cells(1, w) And Cells(x, y + 2) < Cells(2, w) And Cells(x, y + 2) > Cells(1, w) Then
days360temp = Application.WorksheetFunction.Days360(percentDateC, percentDateB)
elapsedTime = elapsedTime + ((days360temp / 360) * (Cells(x, y + 1) / 100))

ElseIf ...

SamT
01-04-2015, 05:56 PM
I couldn't find anything wrong with the code. Run this code and change the comparators until you find what is wrong with the valuse on the worksheet.

Sub test()
Dim percentDateA As Date
Dim percentDateB As Date
Dim percentDateC As Date
Dim percentDateD As Date
Dim elapsedTime As Double
Dim days360temp As Double

For X = 3 To 82
For w = 64 To 68
For y = 28 To 61 Step 2
percentDateA = Cells(X, y)
percentDateB = Cells(X, y + 2)
percentDateC = Cells(1, w)
percentDateD = Cells(2, w)

Dim msg
If percentDateA < percentDateC Then msg = "Test1 "
If percentDateB > percentDateC Then msg = msg & "Test2 "
If percentDateB < percentDateD Then msg = msg & "Test3"
If Len(msg) > 0 Then
MsgBox msg
Stop
End If
msg = ""

'If percentDateA < percentDateC _
And percentDateB > percentDateC _
And percentDateB < percentDateD _
Then
'days360temp = Application.WorksheetFunction.Days360(percentDateC, percentDateB)
'elapsedTime = elapsedTime + ((days360temp / 360) * (Cells(X, y + 1) / 100))

'End If
Next
Next
Next

End Sub

If any condition is true, a message box will tell you which and the code will stop until you press F5. While the code is stopped, hover the cursor over any of the variables to see its value.

ungermkm
01-05-2015, 05:52 AM
I just realized now that my first post wasn't exactly clear- I'm getting the comparator between dates to work, but my days360 function will return 0.

SamT
01-05-2015, 09:52 AM
I just realized that my first post didn't use DAYS360. It was supposed to test your data because I can't find anything else wrong.



Did you run my code?

Did you get at least one MessageBox that said "Test1 Test 2 Test3"?



IF so, edit the code to use




With Application.WorksheetFunction
days360temp = .Days360(.Date(percentDateC), .Date(percentDateB))
End With