Excel Data Validation Prompt Error If overlap

I have came up with the attached solution to check if for a specific person in column A, the dates are not overlap. i want a data validation triggered by formula that if for an individual the user puts overlapped date then it gives a error warning with message overlap dates are not allowed.

see the attached file.
LVL 27
ProfessorJimJamMicrosoft Excel ExpertAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Assuming your entries are going to be in date order, you can set the date validation for each entry based on the maximum value of all items above.

For example the validation for B4 would be:

Min Date:  =MAX($B$2:$C3)+1
Max Date:  =31/12/9999 (or some other max date)

You then set the error messages as required.

When copied down the range to check for the MAX should adjust.

Rob H
ProfessorJimJamMicrosoft Excel ExpertAuthor Commented:
thanks Rob.

i have two issue,  A)  the dates will not be always in order.  B)  the names in column A will not always be James.
it would be different names and what i am looking for is, that if the validation would be also trigger the name as a condtion. for example the date for Mike shall not be checked against dates of James. so only validation sperately for each indivitual in column A.

i hope this is not something very tricky and impossible to achieve.
Rob HensonFinance AnalystCommented:
I think that will be difficult but don't think it would be impossible.

Might be worth putting into words what the equivalent manual check would be and then see if a logic statement can be derived from it.

Rob H
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Ejgil HedegaardCommented:
Use this for B3
ProfessorJimJamMicrosoft Excel ExpertAuthor Commented:
Ejgil,  unfortunately, it did not work.
Rob HensonFinance AnalystCommented:
Logic, I think:

Start Date - Greater than previous finish dates or where less than previous finish dates is also less than the next start date. (i.e. it slots between the finish of one and the start of another)
Finish Date - less than subsequent start dates or greater than all start dates, which by default includes the current entry start date.

Would be a lot easier if the entries were in chronological order.
Rob HensonFinance AnalystCommented:
For the "MAX(IF(...))" formula on a worksheet, I believe it would have to be entered as an array formula (Ctrl + Shift + Enter). Don't know if you can do the same within a Data Validation formula.

Rob H
ProfessorJimJamMicrosoft Excel ExpertAuthor Commented:
i found something in the get-digital-help Oscar's page.  attached. it seems to work, the only problem is that how do i incorporate the condition of column A  names.
Ejgil HedegaardCommented:
It is an array formula, and it has to be used in the data validation in B3 as Min value.
Then it works.
Since it is Max then the order of the dates does not matter.
Ejgil HedegaardCommented:
See sheet
ProfessorJimJamMicrosoft Excel ExpertAuthor Commented:
thanks Ejgil, but see now the attached file.  

highlighted in yellow.  the validation do not take into consideration if the column C end date is greater then COlumn B

see example highlighted for James
Ejgil HedegaardCommented:
Did not observe that the values in C also had to be checked, sorry.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Just re-thinking the logic:

For the entered start date:
Check that the date falls between two previous finish dates or is later than the last finish date.
Where that date falls between two finish dates the date entered has to be less than the start date related to the later of the two finish dates.

So we need formulae that extract 2 or 3 dates:
1) The latest Finish Date which is prior to the entered date
2) The earliest Finish Date which is later than the entered date
3) The start date related to no 2, in other words the earliest start date which is later than the entered date.

The entered start date has to be later than no 1 and prior to no 3.

For the entered finish date:
The entered date also has to be later than no 1 and prior to no 3 as well as being later that the entered start date.

As well as matching for the person!!!
ProfessorJimJamMicrosoft Excel ExpertAuthor Commented:
Hi Rob,

apparently, the solution provided Ejgil Hedegaard worked.  do you think there is better way to do this? if yes, i can open another question.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.