Forum Discussion
Date interval check between columns
So i have dates in a column, B and say there are start and end dates in column D and E, 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,...
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
- Highlight B1 if it falls between dates in D1 and E1/D2 and E2/D3 and E3 and continue this checking till D200 and E200.
- Select B1:B200
- Go to Home (tab) > Conditionnal Formatting > New Rule... > Use a formula...:
=IF(ISNUMBER($B1), IF( SUM(($B1>=D$1:D$200)*(B1<=E$1:E$200)), TRUE))Corresponding sample attached