Forum Discussion
Date interval check between columns
I have to check if date in B1 matches in the interval of D and E, if any date falls in the interval of D and E, need to highlight that.
So i used a lot of formula nothing gives exact result and by using IF it gives SPILL error, just mentioning one that I tried:
AND($B1>=$D$1:$D$200, $B1<=$E$1:$E$200)
This does not give right results. Any suggestion please.
10 Replies
Hi Mahdia_Akter
Assuming I understood (not sure...)
in C1 and copy down as necessary:
=IF( SUM((B1>=D$1:D$200)*(B1<=E$1:E$200)), "Match", "No match")- If i want to place this in conditional formatting so that it auto highlights the cells than I need, will it work? Or do i have to go for countifs?
@Riny_van_Eekelen and I have a different understanding of what needs to be compared to what. Until this is clarified everybody's going to waste time. So, could you please clarify what the formula should do:
- Highlight B1 if it falls between dates in D1 and E1
or
- Highlight B1 if it falls between ANY dates in D1:D200 and E1:E200Thanks
Mahdia_Akter Not sure what the > and < are supposed to do. Especially the semi-colon seems out of place. Perhaps this will work for you
=($B1>=$D$1:$D$200)*($B1<=$E$1:$E$200)
This should spill and array of ones and zeros. 1 indicating that the Date falls between the Start- and End Date, 0 that is does not.
- Thanks, trying with it too.
Mahdia_Akter If it's conditional formatting you need, perhaps the picture below helps.
Green falls within the period. Red does not.
