Solved

Excel Data Validation Prompt Error If overlap

Posted on 2014-10-10
14
191 Views
Last Modified: 2014-10-10
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.
overlap.xlsx
0
Comment
Question by:ProfessorJimJam
  • 5
  • 5
  • 4
14 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40372629
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.

Thanks
Rob H
0
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40372709
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40372804
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.

Thanks
Rob H
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40372966
Use this for B3
=MAX(IF($A$2:A2=A3,$B$2:B2,0))+1
0
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40373014
Ejgil,  unfortunately, it did not work.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40373024
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40373033
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.

Thanks
Rob H
0
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40373044
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.
Prevent-users-from-entering-overlapping-
0
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40373109
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.
0
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40373115
See sheet
overlap.xlsx
0
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40373127
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
overlap--1-.xlsx
0
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40373185
Did not observe that the values in C also had to be checked, sorry.
overlap--1-.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40373415
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!!!
0
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40373531
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.

thanks.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question