Highlight Overlap dates

[sathishsusa]

trying by below steps can i get the code.. to get solve.

have 4 employees, so i have 4 x DateFrom and 4 x DateTo values to deal with.

step 1: get the input data.
create 2 arrays. eg. myDateFrom(1 to 4) and myDateTo(1 to 4) as date arrays, then read your dates into them

Step 2: Test for overlaps
You need to test each date to see if the start date of any employee is >= to the start date of any other, AND <= the end date of any other employee, and vice versa for the end date. Create a boolean variable to hold the results of the test (eg myFlag)

Step 3: Respond to a failed test
If either the start date or the end date are disqualified (myFlag = false), run a message box and force the selection of a new date. else run any msgbox you wish.

Sub MasterSub()
Call step1
Call step2
End Sub

Sub Step1()
some code goes here
End Sub

Sub Step2()
some code goes here
End Sub

[DocAElstein]

Hi sathishsusa

This is a very complicated requirement.

You are asking for a complete solution _.... :-(
_... Generally in such a Help Forum as Excel Fox, we try to help you with particular problems, and not do an entire project for you.

But your latest attempt at explaining is good. Nice explanation of your requirement. Well done _.. :-) It was a nice clear and easy to follow File which you uploaded :-). Very Good :-)
That made it a lot easier for me

_.. So, I can make a start for you, based only loosely on your proposed solution.


Here is a brief description of a code i habe done for you:
I do not know anything about check boxes. :-(

Rem 2) So I look at TRUE or FALSE value in Range("AS8:AS11")
‘2a) take in checked names data into Array
‘2b) from ‘2a) produce
'2b)(i) complete initial string of overlapping names
and
'2b(ii)
( '2b)(i) add all names to initial string of overlapping names )
and
Build Array of all checked as True names, arrChkTrueEmp()

Rem 3) Main Input data
'3a) I use test data Range "B21:E26" – you will need to change as appropriate.
'3b) use 4 arrays for each data column

Rem 4) Main Outer loop # =====Cnt =========
I take each name form column B data
‘4b) Only if I have a name in the Array of all checked as True names, arrChkTrueEmp(), do I consider the current Name , ( Else do nothing and go on to next name in column B )
____If I have a name in the Array of all checked as True names, Then
________ The Inner Loop ‘ -----CntIn-----------
Rem 5) goes “down” all names in column B.
_________ If a name is also in the Array of all checked as True names, arrChkTrueEmp(), the main Criteria check is done
The criteria is either:_..
_.. The name under consideration in the outer loop has a From date within the From and To dates of name under consideration in second inner loop
________ (arrFrm(Cnt, 1) >= arrFrm(CntIn, 1) And arrFrm(Cnt, 1) <= arrTo(CntIn, 1))
Or
_.. The name under consideration in the outer loop has a To date within the From and To dates of name under consideration in second inner loop
________ (arrTo(Cnt, 1) >= arrFrm(CntIn, 1) And arrTo(Cnt, 1) <= arrTo(CntIn, 1))
_____________ If the criteria is met I put a string of "Overlap" in the Array , arrOvrLpDts(CntIn, 1). This is like your idea ...” …Create a boolean variable to hold the results of the test (eg myFlag)...”

Rem 6) Loop through arrOvrLpDts() Array to add to report string and mark cell

Rem 7) Message Box output of report string

[DocAElstein]

Results for code described in last Post. ( I have not done everything you wanted )
( Code is here: http://www.excelfox.com/forum/showth...0178#post10178 )

I have not checked the code extensively. I have not done everything you wanted.
I suggest you go through very carefully and try to understand the code and check and / or modify it to suit exactly what you want.

_.......
I did a quick test on your given data.

This was your given data:

Using Excel 2007 32 bit

Row\Col	AS	AT
7	Check Overlap dates	Employee names
8	WAHR	employee01
9	FALSCH	employee02
10	FALSCH	employee03
11	WAHR	employee04

Worksheet: Sheet1


OverLaptestData.JPG http://imgur.com/aHNfebY
OverLaptestData.jpg

Using Excel 2007 32 bit

Row\Col	B	C	D	E
18	Employee Names	From	To	OVER LAP Dates
19
20
21	employee01	02. Jan 17	15. Jan 17
22	employee03	03. Feb 17	10. Feb 17
23	employee04	10. Jan 17	20. Jan 17
24	employee04	04. Feb 17	10. Feb 17
25	employee02	01. Mrz 17	05. Mrz 17
26	employee04	03. Mrz 17	10. Mrz 17

Worksheet: Sheet1



Here is the results after running my code:( Check boxes checked in AS8 and AS11 to make cell value TRUE . Range object "behind" the cells has that entry pseudo as Range object and the True held As a Boolean , ...)

Using Excel 2007 32 bit

Row\Col	B	C	D	E
18	Employee Names	From	To	OVER LAP Dates
19
20
21	employee01	02. Jan 17	15. Jan 17
22	employee03	03. Feb 17	10. Feb 17
23	employee04	10. Jan 17	20. Jan 17
24	employee04	04. Feb 17	10. Feb 17
25	employee02	01. Mrz 17	05. Mrz 17
26	employee04	03. Mrz 17	10. Mrz 17

Worksheet: Sheet1


And Message Box:
MsgBoxOverlaps.JPG http://imgur.com/3XX7PKT
MsgBoxOverlaps.JPG