SOLVED

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

Copper Contributor

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

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

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

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

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

@Hans Vogelaar 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!

best response confirmed by turndog7 (Copper Contributor)
Solution

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

Try this:

=IF((\$C7<H\$6)*(\$D7>H\$5),"","-")

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

@Hans Vogelaar that got it, thank you very much!!