Master Schedule: Conditional Format based on Date

[Boutspers]

I have a master schedule file to compile projects from various managers. There are four phases with start/end dates. I'd like to be able to enter the dates for the phases, and have the colors change in the cells to the right of the dates based on the entered dates. Is this a stacked conditional formatting formula? Thanks!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
7	Pre-Design		Design		Construction		Close-out		2018												2019
8	Start	Finish	Start	Finish	Start	Finish	Start	Finish	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	05/30/18	10/04/18	10/05/18	06/23/19	06/24/19	08/26/19	08/27/19	11/01/19
10	2017	2017	08/20/18	06/23/19	06/24/19	09/20/19	09/21/19	12/01/19
11	01/29/19	02/22/19	02/23/19	06/04/19	07/15/19	09/30/19	10/01/19	12/01/19
12	10/08/18	12/16/18	12/17/18	06/18/19	07/17/19	10/01/19	10/02/19	12/01/19
13	05/28/18	06/18/18	06/19/18	07/31/19	08/01/19	10/01/19	10/02/19	12/01/19
14	09/25/19	10/05/19	10/06/19	10/31/19	11/01/19	11/30/19	12/01/19	12/31/19
15	09/26/18	01/25/19	01/26/19	10/15/19	11/01/19	12/24/19	01/01/20	02/01/20
16	08/19/19	09/01/19	09/02/19	12/01/19	12/16/19	01/02/20	01/03/20	03/01/20
17	10/01/19	10/20/19	10/21/19	12/01/19	12/16/19	01/02/20	01/03/20	03/01/20
18	09/26/18	01/25/19	01/26/19	02/01/20	tbd	tbd	tbd	tbd
19	11/29/18	01/11/19	01/12/19	02/01/20	tbd	tbd	tbd	tbd
20	09/26/18	12/30/19	tbd	tbd	tbd	tbd	tbd	tbd
21			05/01/20	06/30/20	07/01/20	08/30/20	09/01/20	10/01/20
Sheet1

 

[Peter_SSs]

Is this a stacked conditional formatting formula?

Yes.
This may need a bit of tweaking but should get you headed in the right direction I think.
I8, J8, K8 etc are actual dates (1 Jan 2018, 1 Feb 2018 etc) formatted as "mmm"

Select I9 to end of data and apply these CF rules

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
7	Pre-Design		Design		Construction		Close-out		2018												2019
8	Start	Finish	Start	Finish	Start	Finish	Start	Finish	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	30/05/2018	4/10/2018	5/10/2018	23/06/2019	24/06/2019	26/08/2019	27/08/2019	1/11/2019
10	2017	2017	20/08/2018	23/06/2019	24/06/2019	20/09/2019	21/09/2019	1/12/2019
11	29/01/2019	22/02/2019	23/02/2019	4/06/2019	15/07/2019	30/09/2019	1/10/2019	1/12/2019
12	8/10/2018	16/12/2018	17/12/2018	18/06/2019	17/07/2019	1/10/2019	2/10/2019	1/12/2019
13	28/05/2018	18/06/2018	19/06/2018	31/07/2019	1/08/2019	1/10/2019	2/10/2019	1/12/2019
14	25/09/2019	5/10/2019	6/10/2019	31/10/2019	1/11/2019	30/11/2019	1/12/2019	31/12/2019
15	26/09/2018	25/01/2019	26/01/2019	15/10/2019	1/11/2019	24/12/2019	1/01/2020	1/02/2020
16	19/08/2019	1/09/2019	2/09/2019	1/12/2019	16/12/2019	2/01/2020	3/01/2020	1/03/2020
17	1/10/2019	20/10/2019	21/10/2019	1/12/2019	16/12/2019	2/01/2020	3/01/2020	1/03/2020
18	26/09/2018	25/01/2019	26/01/2019	1/02/2020	tbd	tbd	tbd	tbd
19	29/11/2018	11/01/2019	12/01/2019	1/02/2020	tbd	tbd	tbd	tbd
20	26/09/2018	30/12/2019	tbd	tbd	tbd	tbd	tbd	tbd
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I9	1. / Formula is =AND(ISNUMBER($G9),MEDIAN($G9,$H9,I$8)=I$8)	Abc
I9	2. / Formula is =AND(ISNUMBER($E9),MEDIAN($E9,$F9,I$8)=I$8)	Abc
I9	3. / Formula is =AND(ISNUMBER($C9),MEDIAN($C9,$D9,I$8)=I$8)	Abc
I9	4. / Formula is =AND(ISNUMBER($A9),MEDIAN($A9,$B9,I$8)=I$8)	Abc

 

[Boutspers]

That worked great! The key was changing the months to dates instead of text (but still formatted as 'mmm').
Thank you!!

 

[Peter_SSs]

That worked great! The key was changing the months to dates instead of text (but still formatted as 'mmm').
Thank you!!

You're welcome. Glad that was accurate enough for you.
I did wonder about those couple of uncoloured cells at AA11:AA12 in my screen shot, but then I suppose those two projects do have small breaks between the end of the Design period and the start of the Construction period.