Conditional Formatting for a Project Timeline

[Ferdo258]

Hi, I'm trying to get the conditional formatting for a Project Timeline work right. As you can see cell "I14" or "I11" has 100% but doesn't say so in the displayed timeline, as not everything is orange.
Timeline uses only working dates from Monday to Friday. Unfortunately cannot upload a file here.
The current formula goes as follows =AND($I11>5%, $E11<=K$8,ROUNDDOWN(NETWORKDAYS($E11,$F11)*$I11,0)+$E11+1>=K$8)

Thanks for help.

 

[6StringJazzer]

You are determining net workdays, then multiplying by the % complete. So far so good--that tells you how many workdays should have elapsed.

=AND($I11>5%, $E11<=K$8,ROUNDDOWN(NETWORKDAYS($E11,$F11)*$I11,0)+$E11+1>=K$8)

but then you add that number of workdays to $E11 using a plain vanilla "+", which is adding calendar days. You need to do another workday calculation. Try this:

=AND($I11>5%, $E11<=K$8,WORKDAY($E11+1,ROUNDDOWN(NETWORKDAYS($E11,$F11)*$I11,0))>=K$8)

Obviously not able to test this.

Although this forum does not support file attachments, it does provide an add-in (XL2BB, last button in the edit controls) that you can use in Excel to copy a range, and paste into a post. It will show formatting, formulas, etc. Also you can post a link to a cloud service like OneDrive, Dropbox, Google Docs, etc.

 

[felixstraube]

How about you create two named ranges.
One named "TaskInProgress" with this formula when cell K10 is selected (the selection of the cell is important as these are relative formulas):

=(Sheet1!K$8 >= Sheet1!$E10) * (Sheet1!K$8 <= Sheet1!$F10)

And the other named "TaskCompleted" with this formula when cell K10 is selected:

=(Sheet1!K$8>=Sheet1!$E10)*(Sheet1!K$8<=WORKDAY(Sheet1!$E10,(Sheet1!$G10-1)*Sheet1!$I10))*(Sheet1!$I10>0)

Then you add two conditional formatting rules like this:

You will get this:

ProjectTimeline.xlsx
	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
7							jun-8					jun-15					jun-22					jun-29					jul-6					jul-13
8	Start	Finish	Duration		% Completed		04	05	06	07	08	11	12	13	14	15	18	19	20	21	22	25	26	27	28	29	02	03	04	05	06	09	10	11	12	13
9							L	M	M	J	V	L	M	M	J	V	L	M	M	J	V	L	M	M	J	V	L	M	M	J	V	L	M	M	J	V
10	4/6/2018	6/6/2018	3		0%
11	4/6/2018	6/6/2018	3		10%
12	4/6/2018	6/6/2018	3		90%
13	4/6/2018	6/6/2018	3		100%
14	12/6/2018	25/6/2018	10		0%
15	12/6/2018	25/6/2018	10		10%
16	12/6/2018	25/6/2018	10		50%
17	12/6/2018	25/6/2018	10		90%
18	12/6/2018	25/6/2018	10		100%
19	11/6/2018	4/7/2018	18		0%
20	11/6/2018	4/7/2018	18		1%
21	11/6/2018	4/7/2018	18		100%
Sheet1

Cell Formulas
Range	Formula
K7,P7,U7,Z7,AE7,AJ7	K7	=O8
L8:AN8	L8	=WORKDAY(K8,1)
K9:AN9	K9	=LEFT(UPPER(TEXT(K8,"ddd")),1)
G10:G21	G10	=NETWORKDAYS(E10,F10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K10:AN21	Expression	=TaskCompleted	text	NO
K9:AN21	Expression	=TaskInProgress	text	NO

Here is a working book:

ProjectTimeline.zip

 

[felixstraube]

Sorry the range for the conditional formatting for TaskInProgress should be $K$10:$AN$21 (It doesn't really matter because there are no dates in columns start and finish in row 9, but it should be from row 10 onwards).

 

[Ferdo258]

You are determining net workdays, then multiplying by the % complete. So far so good--that tells you how many workdays should have elapsed.

=AND($I11>5%, $E11<=K$8,ROUNDDOWN(NETWORKDAYS($E11,$F11)*$I11,0)+$E11+1>=K$8)

but then you add that number of workdays to $E11 using a plain vanilla "+", which is adding calendar days. You need to do another workday calculation. Try this:

=AND($I11>5%, $E11<=K$8,WORKDAY($E11+1,ROUNDDOWN(NETWORKDAYS($E11,$F11)*$I11,0))>=K$8)

Obviously not able to test this.

Although this forum does not support file attachments, it does provide an add-in (XL2BB, last button in the edit controls) that you can use in Excel to copy a range, and paste into a post. It will show formatting, formulas, etc. Also you can post a link to a cloud service like OneDrive, Dropbox, Google Docs, etc.

Thanks, this looks the closest to what I was looking for. Maybe I did something wrong, but this time it has the opposite problem, task completed goes beyond task in progress. I have saved files on OneDrive, if you have time would appreciate your help. OneDrive

 

[Ferdo258]

Sorry the range for the conditional formatting for TaskInProgress should be $K$10:$AN$21 (It doesn't really matter because there are no dates in columns start and finish in row 9, but it should be from row 10 onwards).

thanks for you help, very much appreciated. I think this solves the problem with the static timeline, but not so much with the dynamic I'm trying to get.

 

[felixstraube]

Could you explain how the dynamics one will work?

 

[felixstraube]

Could you explain how the dynamics one will work?

 

[6StringJazzer]

Sorry for the long delay. You have not used the word "dynamic" until now. What exactly is dynamic about what you want to do?