DAYS360 in VBA

[ungermkm]

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]

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]

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]

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