Excel - how to use IF/AND/OR combo to see if a date range lies within another date range

Copper Contributor


Screenshot 2023-08-01 at 1.48.12 PM.png

I am trying to write a formula that will see if the acquired date (Column C) OR the sold date (Column D) lie within OR overlap the date range at G6:G7 and so on across the spreadsheet, and return a blank if so and an "-" if  not (basically a checklist to see what indexes I have to check if Person A owned the land in within the years covered by that index. I have googled and tried many permutations of IF, AND and OR functions and can't seem to get a formula to work in all situations.


Thank you!

4 Replies


=IF(OR(AND($C7>=G$5, $C7<=G$6), AND($D7>=G$5, $D7<=G$6)), "", "-")

@HansVogelaar thank you, that works for the "end" of each range, but doesn't capture the in between dates, which is the same problem I've been having, see screenshot.

The first line has the formula, the second is manually entered, which is what I am trying to get to, thanks again for the help!


Screenshot 2023-08-01 at 4.43.29 PM.png

best response confirmed by turndog7 (Copper Contributor)


Try this:



1 best response

Accepted Solutions
best response confirmed by turndog7 (Copper Contributor)