CODEWITHSUNDEEP
excelexcel-formula
Vine
Vine

Reputation: 395

How to check a date is between two data ranges on multiple rows in excel

I have a list of dates, and need a column that indicates whether it is within range of two dates on another sheet. However the kicker is that there are multiple rows on Sheet 2 for the same ID, I need to check if the first row is within range, then if not, when the next row where ID matches.

Sheet 1

[Date]     [ID]   [IsWithinDate]
25-09-2019  4          Yes

Sheet 2

[Start]     [End]      [ID]
19-08-2019  19-09-2019  4
19-09-2019  19-10-2019  4

I expect the outcome for [IsWithinDate] to check the first row, where ID matches, and check that [Date] is between [Start] and [End]. The first row it is not within range, however on row 2 (Sheet 2) it is within range. How do i check multiple rows?

Upvotes: 1

Views: 3112

Answers (2)

Terry W
Terry W

Reputation: 3257

Solution

Suppose you have the following named ranges:

  • Start being all starting dates on your sheet2;
  • End being all ending dates on your sheet2;
  • ID being all IDs on your sheet2.

You can use COUNTIFS to get the answer:

=IF(COUNTIFS(Start,"<="&A3,End,">="&A3,ID,B3)>0,"Yes","No")

Change the cell references as desired. Cheers :)

Upvotes: 3

chris neilsen
chris neilsen

Reputation: 53135

Try this

=IF(SUM((B2=Sheet2!C:C)*(A2>=Sheet2!A:A)*(A2<=Sheet2!B:B))>0,"Yes","No")

(B2=Sheet2!C:C), (A2>=Sheet2!A:A) and (A2<=Sheet2!B:B) return Arrays of TRUE and FALSE. Multiplied together, returns an array of 1's (date is in the range) and 0's date is NOT in the range). SUM'd, if >0 then the date is in at least one of the ranges, with matching ID .

Assumes [Start] and [End] are columns A, B

Upvotes: 1

Related Questions