Identifying Conflicting Project Dates

[KalleeP]

Good Day All

I am creating a spreadsheet to track various training programs, and trying to devise a method to identify if any resource conflicts arise.

I have a Resource name in column E, training start date and end date in columns G & H respectively. I have been trying to set up a formula to check the resource name for duplicates (column E) and if there are any found then verify that the corresponding start and end dates do not conflict. If there is a conflict then output to Column I "Possible Conflict" otherwise jsut leave it blank.

I have been having great difficulties getting my head around this formula and any help would be much appreciated.

Kallee

 

[just_jon]

Book2
	E	F	G	H	I
1	Resource		Start	Stop	Conflict
2	AAA		1/22/2005	1/29/2005
3	AAA		1/28/2005	2/4/2005	Possible
4	CCC		1/22/2005	1/29/2005
5	AAA		2/2/2005	2/9/2005
6	AAA		1/28/2005	2/4/2005	Possible
7
8
9
10
Sheet1

Testing limited to the above sample.

 

[KalleeP]

Thats a start, however it seems to have ommitted Row 2 as a conflict. The end date of Row 2 is in conflict with the start date of row 3.

I will keep playing, any other thoughts?

 

[KalleeP]

THis is what i have come up with so far....

1. Check Column E for repetitive Resource Names
2. For each repitition you must check the start and end dates for conflicts
a) if(and(start date >= any other start date, start date <= any other end date),1,0)

b) if(and(end date >= any other start date, end date <= any other end date),1,0)

therefore if the sum of those 2 equations is greater than 0 then there is a possible conflict, however i am not quite sure how to create a formula to check these to conditions against all duplicate Resources names.

ANy thoughts?