Agile Gantt Chart Modification (no weekends)

[BradMS]

I would like to remove weekends on this chart.
I have accomplished modifying the days (J5 through BJ5) by adding "=J5+IF(WEEKDAY(J5)=6,3,1)". This removes weekends.

I'm stuck on the conditional formatting. As this still seems to use Saturday and Sunday.
Starting on Monday if I enter in 6 in H10 (No. Days), I would like it to fill all the way until Monday.

 

[6StringJazzer]

You have just given a glimmer of what you are doing, so we have to make some assumptions to try to understand it.

From context I assume that row J are dates. Correct?

I also assume that row H is Task Duration. Correct?

I also assume that you have an existing conditional formatting rule, but you don't say what it is, or what the "it" is that you want to fill all the way to Monday.

Can you give more detail about the layout of your worksheet and existing CF rule?

The spoiler is that in your CF rule you probably need to use the WORKDAY function to determine the end date of a task, and use that result to compare to the dates in J to determine where your shading goes. But I can't give you an actual formula without more detail.

 

[BradMS]

Jazzer,
The project is from the default excel templates. If you click new, search agile. you will find it.

 

[BradMS]

the selected cell has conditional formatting of "AND($C8="On Track",I$5>=$F8,I$5<=$F8+$G8-1)"

 

[6StringJazzer]

Only one Agile Gantt template that came up and it doesn't quite match your image but it's close enough that I can figure out what's going on.

Change your CF rule to

=AND($C8="On Track",I$5>=$F8,I$5<=WORKDAY($F8,$G8)-1)

This does not take holidays into account, but neither does the formula for incrementing the date you showed, so they match.

 

[BradMS]

Much appriciated!
However I will need to upload my file.
How do i do this?

 

[6StringJazzer]

This forum does not support file attachments. You can provide a link in a file-sharing cloud service, like Google Docs or Dropbox. You can also install an add-in (see the L2BB link in the toolbar) that can capture a range in a worksheet and reproduce here. You can't test new formulas or code but it makes a lot easier to see what's going on than just a screenshot. Here is an example of the output:

Agile Gantt chart1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
4		Company name		4/15/2021			Legend:		On track					Low risk
5		Project lead
6		Project Start Date:	3/31/2021						April
7		Scrolling Increment:	1						1	2	5	6	7	8	9	12	13	14
8
9		Milestone description	Category	Assigned to	Progress	Start	Days		T	F	M	T	W	T	F	M	T	W
11		TITLE 1
12		Task 1	Goal	Name	25%	3/31/2021	3
13		Task 2	Milestone			4/5/2021	1
14		Task 3	Low Risk		50%	3/28/2021	10
15		Task 4	Milestone			4/20/2021	1
16		Task 5	Med Risk		10%	4/6/2021	8
17		TITLE 2
Light

Cell Formulas
Range	Formula
D4	D4	=WORKDAY(F16,$G16)-1
C6	C6	=IFERROR(IF(MIN(Milestones4352[Start])=0,TODAY(),B11(Milestones4352[Start])),TODAY())
I6	I6	=TEXT(I7,"mmmm")
P6	P6	=IF(TEXT(P7,"mmmm")=I6,"",TEXT(P7,"mmmm"))
I7	I7	=IFERROR(Project_Start+Scrolling_Increment,TODAY())
J7:R7	J7	=I7+IF(WEEKDAY(I7)=6,3,1)
I9:R9	I9	=LEFT(TEXT(I7,"ddd"),1)
I11:R17	I11	=IF(AND($C11="Goal",I$7>=$F11,I$7<=$F11+$G11-1),2,IF(AND($C11="Milestone",I$7>=$F11,I$7<=$F11+$G11-1),1,""))
F12	F12	=TODAY()
F13	F13	=TODAY()+5
F14	F14	=F12-3
F15	F15	=F12+20
F16	F16	=F12+6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I7:BL36	Expression	=AND(TODAY()>=I$7,TODAY()<J$7)	text	NO
I6:BL6	Expression	=AND(I$7<=EOMONTH($I$7,1),I$7>EOMONTH($I$7,0))	text	NO
J6:BL6	Expression	=AND(J$7<=EOMONTH($I$7,2),J$7>EOMONTH($I$7,0),J$7>EOMONTH($I$7,1))	text	NO
I6:AM6	Expression	=I$7<=EOMONTH($I$7,0)	text	NO
E9:E36	Other Type	DataBar		NO
I10:BL35	Other Type	Icon set		NO
I10:BL35	Expression	=AND($C10="Low Risk",I$7>=$F10,I$7<=$F10+$G10-1)	text	YES
I10:BL35	Expression	=AND($C10="High Risk",I$7>=$F10,I$7<=$F10+$G10-1)	text	YES
I10:BL35	Expression	=AND($C10="On Track",I$7>=$F10,I$7<=$F10+$G10-1)	text	YES
I10:BL35	Expression	=AND($C10="Med Risk",I$7>=$F10,I$7<=$F10+$G10-1)	text	YES
I10:BL35	Expression	=AND(LEN($C10)=0,I$7>=$F10,I$7<=$F10+$G10-1)	text	YES

 

[BradMS]

I tried the L2BB but it kept freezing.
I will upload a link

 

[BradMS]

Only one Agile Gantt template that came up and it doesn't quite match your image but it's close enough that I can figure out what's going on.

Change your CF rule to

=AND($C8="On Track",I$5>=$F8,I$5<=WORKDAY($F8,$G8)-1)

This does not take holidays into account, but neither does the formula for incrementing the date you showed, so they match.

I'm so sorry. Your solution ended up working!!
I added a column which messed everything up

Another addition I would like to add is
To the left In (A) of Tasks, would I be able to add a number system? When changing the number, it will re organize the list to change the timing order? In your example, task 3 is first then 5, what if I would like to make 5 before 3 without changing the date or any info? how much work would this be?