Inconsistency in date ranges

[spoursy]

Checking for inconsistency in date ranges
Please see the attached sample worksheet
I have 4 date ranges B2:C2, E2:F2, H2:I2, K2:L2 It isn’t necessary for all to be filled, and often they wouldn’t be
In N2 I need a formula which will tell me if there is an inconsistency in dates. In the example there is an inconsistency between I2 & K2
In P2 I want to put a date and check if its value is covered by the date ranges in B2:C2, E2:F2, H2:I2, K2:L2
Any thoughts?

[sandy666]

try this:

Please Login or Register  to view this content.

it will show address (if found and where) or not found.

Edit:
ups, I lost "inconsistency between I2 & K2"

Please Login or Register  to view this content.

or shorter:

Please Login or Register  to view this content.

if TRUE - dates are OK, if FALSE - inconsitency

[spoursy]

Hi

Thanks for your replies.

The
=AND(DATEDIF(B2,C2,"d")=7,DATEDIF(E2,F2,"d")=7,DATEDIF(H2,I2,"d")=7,DATEDIF(K2,L2,"d")=7,DATEDIF(C2,E2,"d")=1,DATEDIF(F2,H2,"d")=1,DATEDIF(I2,K2,"d")=1)
formula works in part but
1. only when all 4 sets of date ranges are entered
and
2. only if the date ranges are 7 days each

Any thoughts?

[sandy666]

7 because it was your input (B2+7, etc.), so I assumed it must be 7

in this case (with your xlsx and formula B2+7, etc.) you have many possibilities with blank cell(s), with wrong date(s), with blank cell(s) and wrong date(s)... etc
you need to define all criteria and use IF or CHOOSE function

if every value of cell can be different you've 6435 possibilities (or more, I suppose)