Excel GANTT spreadsheet

[ginstermanuk]

Hi,
I am trying to make a GANTT using Excel as shown below using Conditional Formatting to show the Plan Start - Plan Finish, what conditional formatting arguments do i need to enter to account for an over run in the Actual Finish. i.e if the Stage 1 - Initiating should have finished at wk4 but it actually finished at wk 5 id like the week 5 to be a different colour so i can call this in the key "Task overrun" or something like that. Also is there anyway that if the percentage is 100% it turns the coloured bar another colour denoting complete.

Thanks for any help

Month	Jan-25	Feb-25
W/C	06/01/2025	13/01/2025	20/01/2025	27/01/2025	03/02/2025	10/02/2025	17/02/2025	24/02/2025
Activity	Plan Start	Plan Finish	Plan Duration	Actual Start	Actual Finish	Actual Duration	Percent Complete	2	3	4	5	6	7	8	9
Stage 1: Initiating	2	4	2	2	5	3	100.00%
	3	5	2	3	5	2	80.00%
	4	7	3	4	8	4	70.00%

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you also use XL2BB (see below) to show us what the end results would look like after you have filled the desired cells with colour manually?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ginstermanuk]

Hi,
Thanks for your reply, i have updated my account details but i am unable to add in any add-ins due to my workplace restrictions

i apologise but ive had to paste below what i think you have asked for

Project Plan GANTT
Key:		Planned Duration		Task Overrun		Task complete
Month	Jan-25	Feb-25	Mar-25
W/C	06/01/2025	13/01/2025	20/01/2025	27/01/2025	03/02/2025	10/02/2025	17/02/2025	24/02/2025	03/03/2025	10/03/2025	17/03/2025	24/03/2025
Critical Path (Y/N)	Activity	Plan Start	Plan Finish	Plan Duration	Actual Start	Actual Finish	Actual Duration	Percent Complete	2	3	4	5	6	7	8	9	10	11	12	13
	Stage 1: Initiating	2	4	2	2	5	3	100.00%
		3	5	2	3	5	2	80.00%
		4	7	3	4	8	4	70.00%
	Stage 2: Planning

 

[Peter_SSs]

i have updated my account details

Thanks for that.

but i am unable to add in any add-ins due to my workplace restrictions

Fair enough. For any future threads you start, I suggest you make that point in post 1 so we don't keep asking,

what i think you have asked for

Not quite. I would like to see the expected Gantt chart. Instead, can you manually fill in the colours on your worksheet as you would want and then take a snip of that and paste that into a post here so we can see your goal?
If you can, include the column and row labels in your snip so we can see just where things are?

 

[ginstermanuk]

Hope this helps

 

[Peter_SSs]

Thanks for the image. See if this is what you want then.
Not sure what you would want if Actual Start was later/earlier than Plan Start or if Actual Finish was before Plan Finish.
If any of those are possible I would want to see examples of the colours you would want for each situation.

25 01 27.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
6	Plan Start	Plan Finish	Plan Duration	Actual Start	Actual Finish	Actual Duration	Percent Complete	2	3	4	5	6	7	8	9	10	11	12	13
7	2	4	2	2	5	3	100.00%
8	3	5	2	3	5	2	80.00%
9	4	7	3	4	8	4	70.00%
Gantt

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I7:I9	Expression	=I7=1	text	NO
J7:U9	Expression	=AND(J$6>=$C7,J$6<=$D7)	text	NO
J7:U9	Expression	=AND(J$6>$D7,J$6<=$G7)	text	NO