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,...
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:E200
Thanks
- 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
- THANK YOU SO MUCH, i guess it worked. Can you please help me out with another thing?
So the first, middle and last name is all jumbled up here, like in one worksheet name is "Jack Hunter John", another worksheet name of the same person is "John Jack Hunter", or like "Emma Stone Watson" is written in another sheet as Stone Emma, Is there any way I can match the names based on the strings here like Emma and Jack