Fill Column cells based on Start and End date with Date in cell

[andyka]

Hi

I need help creating something similar to a Gantt.
I have Start date and End dates with activity status.
I want columns to be filled with color based on the key between the start and end date under correct column. Is it possible also to add start and end date on the colored cell.
Also let you know that some task only have either Start or End Date.
Please Help!
Thank you in advance

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	KEY	Not Started
2		InProgress
3		Completed
4	Activity Name	Start	Finish	Activity Status	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22
5	29-Oct-21
6	10670.5.11 OST - P1
7	SG 0 - TRA (10%)	02/10/21	29/11/21	Not Started	02/10/21	29/11/21
8	Stage Gate 0 Complete		29/11/21	Not Started
9	Start Definition Phase	12/10/21		Completed
10	10670.5.12 OST - P2
11	SG 0 - TRA (10%)	14/10/21	29/12/21	InProgress
12	Stage Gate 0 Complete		29/11/21	Not Started
13	Stage Gate 1 Approved		29/12/21	Not Started
14	Summary - Concept (Planning Package)	22/12/21	20/02/22	Not Started
15	SG0 to SG1 LOE	18/11/21	15/04/22	Not Started
16	10670.5.15 OST - 07
17	Receive Expression of Interest from AWE Sponsor	29/10/21		Completed	29/10/21
18	Start	18/12/21		Completed
19	02-Nov-21
20	10670.5.12 OST - P2
21	Update Risk Register	14/11/21	02/09/22	Not Started		14/11/21										02/09/22
22	Update MDAL	28/03/21	02/05/21	Not Started
23	04-Nov-21
24	10670.5.12 OST - P2
25	Risk Register Review	23/10/21	04/06/22	Not Started
26
Sheet1

 

[andyka]

Any Expert Help Please!

 

[ExceLoki]

try this
----------------------

Fill Column cells based on Start and End date with Date in cell.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	KEY	Not Started
2		InProgress
3		Completed
4	Activity Name	Start	Finish	Activity Status	10/1/2021	11/1/2021	12/1/2021	1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022
5	29-Oct-21
6	10670.5.11 OST - P1
7	SG 0 - TRA (10%)	10/2/2021	11/29/2021	Not Started	10/2/2021	11/29/2021
8	Stage Gate 0 Complete		11/29/2021	Not Started		11/29/2021
9	Start Definition Phase	10/12/2021		Completed	10/12/2021
10	10670.5.12 OST - P2
11	SG 0 - TRA (10%)	10/14/2021	12/29/2021	InProgress	10/14/2021		12/29/2021
12	Stage Gate 0 Complete		11/29/2021	Not Started		11/29/2021
13	Stage Gate 1 Approved		12/29/2021	Not Started			12/29/2021
14	Summary - Concept (Planning Package)	12/22/2021	2/20/2022	Not Started			12/22/2021		2/20/2022
15	SG0 to SG1 LOE	11/18/2021	4/15/2022	Not Started		11/18/2021					4/15/2022
16	10670.5.15 OST - 07
17	Receive Expression of Interest from AWE Sponsor	10/29/2021		Completed	10/29/2021
18	Start	12/18/2021		Completed			12/18/2021
19	02-Nov-21
20	10670.5.12 OST - P2
21	Update Risk Register	11/14/2021	9/2/2022	Not Started		11/14/2021										9/2/2022
22	Update MDAL	3/28/2021	5/2/2021	Not Started
23	04-Nov-21
24	10670.5.12 OST - P2
25	Risk Register Review	10/23/2021	6/4/2022	Not Started	10/23/2021								6/4/2022
working

Cell Formulas
Range	Formula
E7:R25	E7	=IFERROR(IFS(DATE(YEAR($B7),MONTH($B7),1)=E$4,$B7,DATE(YEAR($C7),MONTH($C7),1)=E$4,$C7),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:R25	Expression	=AND($D7=$B$1,IFS(AND($B7<>"",$C7<>""),AND(E$4>=DATE(YEAR($B7),MONTH($B7),1),E$4<=DATE(YEAR($C7),MONTH($C7),1)),AND($B7="",$C7=""),"",OR($B7="",$C7=""),IF(DATE(YEAR(MAX($B7,$C7)),MONTH(MAX($B7,$C7)),DAY(1))=E$4,TRUE)))	text	NO
E7:R25	Expression	=AND($D7=$B$2,IFS(AND($B7<>"",$C7<>""),AND(E$4>=DATE(YEAR($B7),MONTH($B7),1),E$4<=DATE(YEAR($C7),MONTH($C7),1)),AND($B7="",$C7=""),"",OR($B7="",$C7=""),IF(DATE(YEAR(MAX($B7,$C7)),MONTH(MAX($B7,$C7)),DAY(1))=E$4,TRUE)))	text	NO
E7:R25	Expression	=AND($D7=$B$3,IFS(AND($B7<>"",$C7<>""),AND(E$4>=DATE(YEAR($B7),MONTH($B7),1),E$4<=DATE(YEAR($C7),MONTH($C7),1)),AND($B7="",$C7=""),"",OR($B7="",$C7=""),IF(DATE(YEAR(MAX($B7,$C7)),MONTH(MAX($B7,$C7)),DAY(1))=E$4,TRUE)))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D7:D9	List	=$B$1:$B$3
D11:D15	List	=$B$1:$B$3
D21:D22	List	=$B$1:$B$3
D25	List	=$B$1:$B$3
D17:D18	List	=$B$1:$B$3

 

[andyka]

Good Morning ExceLoki
Thank you so much. You're a Star. Problem Solved

 

[andyka]

Good Morning ExceLoki
Not solved yet I am missing format and dates on row number 22. I amended the formula to start formula and format from Cell E5 as I may have data in Cell E5. Please help. Thank you in Advance

Gantt with dates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	KEY	Not Started
2		InProgress
3		Completed
4	Activity Name	Start	Finish	Activity Status	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22
5	29-Oct-21
6	10670.5.11 OST - P1
7	SG 0 - TRA (10%)	02/10/21	29/11/21	Not Started	02/10/21	29/11/21
8	Stage Gate 0 Complete		29/11/21	InProgress		29/11/21
9	Start Definition Phase	12/10/21		Completed	12/10/21
10	10670.5.12 OST - P2
11	SG 0 - TRA (10%)	14/10/21	29/12/21	InProgress	14/10/21		29/12/21
12	Stage Gate 0 Complete		29/11/21	Not Started		29/11/21
13	Stage Gate 1 Approved		29/12/21	Not Started			29/12/21
14	Summary - Concept (Planning Package)	22/12/21	20/02/22	Not Started			22/12/21		20/02/22
15	SG0 to SG1 LOE	18/11/21	15/04/22	Not Started		18/11/21					15/04/22
16	10670.5.15 OST - 07
17	Receive Expression of Interest from AWE Sponsor	29/10/21		Completed	29/10/21
18	Start	18/12/21		Completed			18/12/21
19	02-Nov-21
20	10670.5.12 OST - P2
21	Update Risk Register	14/11/21	02/09/22	Not Started		14/11/21										02/09/22
22	Update MDAL	28/03/21	02/05/21	Not Started
23	04-Nov-21
24	10670.5.12 OST - P2
25	Risk Register Review	23/10/21	04/06/22	Not Started	23/10/21								04/06/22
26
27
28
29	Blue - E5:R25 '=AND($D5=$B$1,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE))) Amber - E5:R25 =AND($D5=$B$2,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE))) Green - E5:R25 =AND($D5=$B$3,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))
30
31
32
Sheet1 (2)

Cell Formulas
Range	Formula
E5:R25	E5	=IFERROR(IFS(DATE(YEAR($B5),MONTH($B5),1)=E$4,$B5,DATE(YEAR($C5),MONTH($C5),1)=E$4,$C5),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:R25	Expression	=AND($D5=$B$2,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO
E5:R25	Expression	=AND($D5=$B$3,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO
E5:R25	Expression	=AND($D5=$B$1,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE)))	text	NO

 

[andyka]

Hi ExceLoki
My Bad. It working. I spotted the obvious reason which is the date in ROW 22 is earlier then the date starting in Column E4. I have now changed the date year and now it works
thank you a million You're a Star.

 

[ExceLoki]

happy to help. thanks for the feedback