date - Index and match with an additional condition (excel only)

Question

Continuing this topic, I would like to add one more condition. I have the following start, end, and action columns:

11/9/2007   10/10/2008  SELL
11/14/2008  5/29/2009   BUY
11/27/2009  2/5/2010    SELL
10/8/2010   4/8/2011    SELL

I also have target days (ex-weekends) starting from April 1, 2007 to today. I would like to do the following:

• If the target day is in the range of start and end dates AND action is SELL, then print out -1;
• If the target day is in the range of start and end dates AND action is BUY, then print out 1;
• If the target day is not in the range of start and end dates, then print out 0;

Any guidance/tips appreciated as always.

Answer 1

I think this code will help you problem

Dim dStart As Date
Dim dEnd As Date
Dim dDate As Date
Dim iCol As Integer

iCol = 2

Do While Cells(iCol, 2).Value <> ""
    dStart = Format(Cells(iCol, 2).Value, "mm/dd/yyyy")
    dEnd = Format(Cells(iCol, 3).Value, "mm/dd/yyyy")
    if dDate > dStart and dDate < dEnd then
        if Cell(iCol,4).Value = "SELL" then
            printout -1
        else
            printout 1
        end if
    else
        printout 0
    end if
    iCol = iCol + 1
Loop

If you just only need excel function

=IF(AND(J3 >= F3,J3 <= G3, H3="SELL"),-1,IF(AND(J3 >= F3,J3 <= G3, H3="BUY"),1,0))