CODEWITHSUNDEEP
exceldateconditional-statements
AK88
AK88

Reputation: 3026

Rows with dates between multiple conditions (excel only)

I have Column A:

str 11/9/2007
end 10/10/2008
str 11/14/2008
end 5/29/2009
str 11/27/2009
end 2/5/2010
str 10/8/2010
end 4/8/2011
str 4/29/2011
end 5/20/2011
str 7/8/2011
end 10/21/2011

and Column B:

9/6/2007
9/7/2007
9/10/2007
9/11/2007
9/12/2007
9/13/2007
9/14/2007
9/17/2007
9/18/2007
9/19/2007
9/20/2007
9/21/2007
9/24/2007
9/25/2007
9/26/2007
9/27/2007
9/28/2007
10/1/2007
10/2/2007
10/3/2007
10/4/2007
10/5/2007
10/8/2007
10/9/2007
10/10/2007
10/11/2007
10/12/2007
10/15/2007
10/16/2007

I'd like to insert 1 in the column next to Column B if Column B date is in any of the start and end range in Column A. So the expected output is something like:

0
0
0
1
1
1
1
1
1
1
1
1
1
1

Any tips, please?

Upvotes: 0

Views: 66

Answers (2)

dwirony
dwirony

Reputation: 5450

Sub CheckingDates()
Dim Value1 As Date, Value2 As Date, Value3 As Date

For j = 1 To 12 Step 2
    For i = 1 To 29
        Value1 = Right(Range("A" & j), Len(Range("A" & j)) - 4)
        Value2 = Right(Range("A" & j + 1), Len(Range("A" & j + 1)) - 4)
        Value3 = Range("B" & i).Value
            If Value3 > Value1 And Value3 < Value2 Then
                Range("C" & i).Value = "1"
            End If
    Next i
Next j
End Sub

Upvotes: 1

DMM
DMM

Reputation: 1112

Let s(i) and e(i) denote your start and end dates (i=1,2,3,...,N) and t a target date.

You need to find if there exists a value of i such that s(i)<=t<=e(i).

You can use the MATCH function to do this.

The s(i) and e(i) (i=1,2,3,...,N) should each be arranged (as suggested by commenter @pnuts) in a column in ascending order. Let Start and End respectively denote the ranges containing these columns of start and end dates. Let Target denote a target date, t.

=MATCH(Target,Start,1) returns the index of the date within Start which is the latest date which is on or before the target date. So it returns a candidate value for the index, i such that s(i)<=t. All that remains is to test whether e(i)>=t for this candidate value, ie

=INDEX(End,MATCH(Target,Start,1))>=Target

Generally, this will deliver TRUE if there exists a value of i such that s(i)<=t<=e(i) and FALSE otherwise. The exception to this occurs when the target date t occurs before the first start date s(1). In this case the error value #N/A is delivered. You can trap this with the IFERROR function to deliver FALSE instead and you can convert the booleans TRUE and FALSE to the 0 and 1 result you are looking for using the usual -- (double negation) operation.

It is important that the start and end dates are arranged in ascending (earliest first) order. If not this solution will not work.

Upvotes: 1

Related Questions