Modifying Gantt Chart

[ChaosPup]

Hi all, I'm creating a Gantt chart for a 90 plan for a new job. I downloaded the basic Gantt template and tweaked it a bit, but I've gotten turned around trying to add in a feature I want and I hope I can get some advice!

I'd like to rename the 'Days' column to 'Planned Days', add in an extra column between F and G for 'Actual Days', and to modify the conditional formatting to highlight only the extra days red on the end of the blue bar.

As an example, in the image, if I were to enter 20 in the new 'Actual Days' column, I'd like it to add the correct number of days to the 'Work Days' column and add a red bar from Tuesday 17th to Saturday 24th.

As I said, I'm getting a bit stuck with it so all advice is welcome! Thanks!

 

[DanteAmor]

Hi @ChaosPup, I hope you are well.

Tuesday 17th to Saturday 24th.

Do you mean Saturday, October 21?

add in an extra column between F and G for 'Actual Days'

If we add a column, then now the dates will start in column K.

Try the following:

Dante Amor Gantt.xlsm
	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	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1
2
3
4		Start Date		02/10/2023
5											2 oct 2023							9 oct 2023							16 oct 2023							23 oct 2023							30 oct 2023
6											2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	1	2	3	4	5
7				START	END	Planned Days	Actual Days	Work Days			M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W	T	F	S	S
8
9	1	TASK		02/10/2023	16/10/2023	15	20	11
10	1.1	TASK		17/10/2023	24/10/2023	8	9	6
11	1.2	TASK		25/10/2023	28/10/2023	4	4	3
12	1.3	TASK		29/10/2023	05/11/2023	8	6	5
gantt 2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K9:AY100	Expression	=Y(K$6>=$D9,K$6<=$E9)	text	NO
K9:AY100	Expression	=Y(K$6>=$D9,K$6<=$D9+$G9-1)	text	NO

Note:

The formula for the blue format is an assumption of how you have it.​

But use the formula for the red format. The new rule should be below the blue format rule.​

You must also adjust the cell range to apply the format to your sheet, I put K9:AY100 for the example.​

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[ChaosPup]

Hi, thanks for you reply. Unfortunately that didn't work for me, I've taken a screenshot of the conditional formatting as it stands in case it's causing the issue.

 

[ChaosPup]

That's it fixed now, I changed the expression for Red to the same format as blue and added the extra '+G9-1' part. Thanks!!

 

[ChaosPup]

I've actually had another thought - is it possible to do the same for days under? So if I have planned 10 days and it only takes 5, can I 'back-fill' five days a different colour?

 

[DanteAmor]

So if I have planned 10 days and it only takes 5, can I 'back-fill' five days a different colour?

Add a conditional format to the beginning of the rules with the following formula:

=Y(K$6>=$D9,K$6<=$E9,K$6>=TODAY())

 

[ChaosPup]

Hi, thanks again for replying. I tried adding that new rule to the top of the list, but all it does is turn everything green. I tried moving it down the list a step at a time, but with no luck. I then tried changing the format of the expression to match the other expressions but that didn't work either. I get the feeling I'm doing something obviously wrong here, I just can't figure out what it is!

 

[DanteAmor]

So if I have planned 10 days and it only takes 5, can I 'back-fill' five days a different colour?

Your new request is not clear to me, I don't know if there are 5 days left from today's date or from what?

I recommend you create a new thread, in the new thread, please explain with example and images what your requirement is.

Regards
Dante Amor

 

[ChaosPup]

Hi, sorry I didn't explain myself very well.

In the screenshots below, I've used row 9 as an example and manually changed the colour of the cells so I can explain better.

In the first screenshot, you can see that there is no 'Actual Days' value entered, so the bar only appears in blue. In the second screenshot, I've entered 6 as the 'Actual Days', so the last 6 cells of the bar have changed to green. If I'd entered 10 as the 'Actual Days', the last 2 cells of the bar would have changed to green, and if I'd entered 3 as the 'Actual Days' the last 9 cells of the bar would have changed to green, etc. I hope this explains it better.