Excel validation rule help with date ranges

Excel 2010 question: I have a table that I'm trying to write a validation formula (using Excel formula and avoid VBA if possible)for. In the example table below - the "Assignment" given to the unique combination of "Account" & "Sub-account" for an over-lapping Start and End Date must be the same. A conflict error is thrown if that rule is violated. So, "2" is not a conflict because the date range is within "1". "3" is not a conflict because there is no overlapping date range. "4" not a conflict because it is a different "Account" & "Sub-Account" combination. "5" is a conflict because a different "Assignment" is given for the same "Account" & "Sub-Account" and Date range.

NO. Account Sub-account Start Date End Date Assignment CONFLICT?
1 PET DOG 1/1/2012 1/31/2012 HOUSE1
2 PET DOG 1/15/2012 1/30/2012 HOUSE1 NO
3 PET DOG 2/1/2012 2/29/2012 HOUSE3 NO
4 PET CAT 2/1/2012 2/29/2012 HOUSE3 NO
5 PET DOG 1/1/2012 1/31/2012 HOUSE2 YES

Thanks for the quick response Dilip!
I noticed that when I added 2 more conditions (6 & 7 - see attached xlsx), the formula failed:
Any idea what I'm doing wrong?


NO. Account Sub-account Start Date End Date Assignment CONFLICT?
1 PET DOG 1/1/2012 1/31/2012 HOUSE1
2 PET DOG 1/15/2012 1/30/2012 HOUSE1 No
3 PET DOG 2/1/2012 2/29/2012 HOUSE3 No
4 PET CAT 2/1/2012 2/29/2012 HOUSE3 No
5 PET DOG 1/1/2012 1/31/2012 HOUSE2 Yes
6 PET CAT 2/15/2012 2/29/2012 HOUSE1 No <--Should fail, since conflicts with No. 4.
7 PET CAT 2/15/2012 3/15/2012 HOUSE2 No <--Should fail, since conflicts with No. 4.

Sorry for the confusion - Row 6 should cause a conflict because "PET" & "CAT" was given an assignment of "House1" in but given assignment of "House3" in Row 4 for an over-lapping date range. Same applies for Row 7. The assignment for "PET" & "CAT" can be something other than "House3" only if there is no date over-lap. For example; if the date range was 3/1/2012 to 3/31/2012 - this would not be considered a conflict.

I found a solution to this - it is an array formula that checks if there is a conflicting assignment given to the same Account & Sub-account combination, then checks if there is a date overlap as well. It will generate a conflict if there is a conflicting assigment and a date over-lap only:

Please Login or Register  to view this content.