Search for a date within a Date range in excel

1
votes

I'm looking for a way to search for a cell within range of cells in a table using excel, so i need to look for Date-Time within a Range in table that has large amount of rows,

These pics attached show it all; the first pic shows what is the current scenario; I'm using the IF statement to know if the Date was found/Contained/included in range and If it was found then put "1" other than this put "0"
I have used this If statement before: =IF(AND(AC5>=AE5,AC5<=AF5),"1","0")

But the thing is its comparing each Row by Row, not Cell by Row, That is unwanted, The thing is i want to compare each cell by all of the ranges in the table and upon this it puts "1" or "0".

Please find the attached screenshots to clarify the question : Current Scenario Example My Question

Any Solutions please?

1
excelif-statementsearchexcel-formulacell
try this link-> extendoffice.com/documents/excel/… - Nitin Dhomse
Recommendations? Hard to do. Add a screenshot where you mock up the expected result. Row by row is easy, as you have done with your formula. It is not clear what you want to see as the final output. - teylyn
Do you need to know where the matching row is in XYZ, or just that it has a matching row somewhere? - Tom Sharpe
Actually i don't know where is the matching ones, the rows are above 17,000 its large amount of data, that's why the pivot table is not a solution. - Dee S

1 Answers

0
votes

Can you try with COUNTIFS function? In the example attached here, I have cell A & B for dateranges. E has Dates. I used to COUNTIFS in column F1 = COUNTIFS(A:A,"<="&E1,B:B,">="&E1) to identify whether dates in E within range A & B. If countifs return 1 or more, then it means data in E are within range.