Date Range Check Formula

[david.x.weekes]

Hi - looking for some help...

I have a need to compare two date ranges to see if any of the dates in one range fall within the other range.

I have attached an example sheet to show what my desired outcome is.

Column E should check if any of the dates between the range in column A and Column B fall in the date range of Column C and Column D. Column E should return Yes if they do and No if they do not.

Thanks in advance

[ben_hensel]

Try this in E2:

Please Login or Register  to view this content.

[david.x.weekes]

Thanks for that - it works :-)

One anomaly that I've noticed is that if cells A,B,C,D are all blank then cell E returns a "Yes" - ideally, I'd like Cell E to be either Blank or return a "No"...

[ben_hensel]

Well that would just be wrapping an IF like this maybe:

Please Login or Register  to view this content.

That will return a blank string "" if all four cells A2:D2 are blank.

Note there's other ways to get errors here too, a blank will be assessed as Jan 0th, 1900 basically (or Dec 31 1899 I suppose), so think about how that would interact with a one-blank situation or similar. And there's no handling for what would implicitly be nonsense data like end date being before start date, so there's a bunch more tests you might want to string into an AND on that first IF test.