Conditional formatting formula

[Mrlewis]

Hi All,
I have been modifying a project spreadsheet and have a small issue. I'm using the conditional formatting with a formula that uses the percentage in another cell and the date range in other cells to colour milestones up to a certain date. It's seems to get to the milestone date only when I input 130 percent as opposed to 100 percent. Can anyone help with this please?

 

[Mrlewis]

=AND($J11>5%,$F11<=L$8,ROUND(NETWORKDAYS.INTL($F11,$G11)*$J11,0)+$F11+1>=L$8)

The above is the current formula in the conditional formatting.

The other arguments count the days of a given task and highlight the completed percentage of those days at the given task date range on the task rows. I'm not a total expert on this. It appears to be calculating the percentage of what is highlighted slightly short of the final date for each task. It seems to fall short when at 100%. 130% seems to bring the cells to highlight for the full task date range.

 

[Joe4]

OK, but what about my explanation back in post 5, in looking at your second argument? It looks like you haven't updated this part of the formula.

[COLOR=#333333]$F11<=L$8[/COLOR]

L8 is a single number, not a formatted date right?
If so, then this logic will not work properly, as you are comparing a date to a single number.

 

[Mrlewis]

It would appear so. Should I change L8 to a date from what it's doing currently or what would you recommend?
The 4 columns after L8 do the same with the exception of one which looks at the date in Jan. I had to do this so the date numbers follow correctly against the days on my table.
It currently looks at a date next to it and works out that it's the 31st of dec.
What puzzles me is that the conditional formatting highlights the correct start date of each task no matter where it falls date wise when I put more than 5% in the other cell.
I will do a snip of that aspect of the spreadsheet if it helps?

 

[Mrlewis]

I did update it and it highlighted all of the L column task lines even if it wasn't the start date, so I reverted it back

 

[Joe4]

Should I change L8 to a date from what it's doing currently or what would you recommend?

I gave you a recommendation back in post 5.

Let's confirm what really is in F8. What format do you currently have on that cell?
If you change the format to "General", does it change the number that is shown in the cell?

I did update it and it highlighted all of the L column task lines even if it wasn't the start date, so I reverted it back

But you said that it wasn't working, so reverting back is reverting it back to something that isn't working right?
There may be more than one issue going on here.

If possible, it might be best if you could upload the file to a file sharing site and provide a link, so we can check out all the underlying details for ourselves.

 

[Mrlewis]

F8 has Start in the cell and is set to general.

It wasn't fully working interms of 100% selected in column J.

Which file sharing site do you recommend?

 

[Mrlewis]

Here is the area of interest

Book1
	L	M	N	O	P	Q	R
5	Jan-4
6
7
Project Tracker

Cell Formulas
Range	Formula
L5	=CHOOSE(WEEKDAY(D6+(I6-1)*7),5,4,3,2,1,0,6)+D6+(I6-1)*7
L8	=M8-1
L9	=CHOOSE(WEEKDAY(L8,1),"S","M","T","W","T","F","S")
S5	=L5+7
S8	=WORKDAY.INTL(R8,1)
S9	=CHOOSE(WEEKDAY(S8,1),"S","M","T","W","T","F","S")
Z5	=S5+7
Z8	=WORKDAY.INTL(Y8,1)
Z9	=CHOOSE(WEEKDAY(Z8,1),"S","M","T","W","T","F","S")
M8	=N8-1
M9	=CHOOSE(WEEKDAY(M8,1),"S","M","T","W","T","F","S")
N8	=O8-1
N9	=CHOOSE(WEEKDAY(N8,1),"S","M","T","W","T","F","S")
O8	=P8-1
O9	=CHOOSE(WEEKDAY(O8,1),"S","M","T","W","T","F","S")
P8	=L5
P9	=CHOOSE(WEEKDAY(P8,1),"S","M","T","W","T","F","S")
Q8	=WORKDAY.INTL(P8,1,2)
Q9	=CHOOSE(WEEKDAY(Q8,1),"S","M","T","W","T","F","S")
R8	=WORKDAY.INTL(Q8,1,3)
R9	=CHOOSE(WEEKDAY(R8,1),"S","M","T","W","T","F","S")
T8	=WORKDAY.INTL(S8,1)
T9	=CHOOSE(WEEKDAY(T8,1),"S","M","T","W","T","F","S")
U8	=WORKDAY.INTL(T8,1)
U9	=CHOOSE(WEEKDAY(U8,1),"S","M","T","W","T","F","S")
V8	=WORKDAY.INTL(U8,1)
V9	=CHOOSE(WEEKDAY(V8,1),"S","M","T","W","T","F","S")
W8	=WORKDAY.INTL(V8,1)
W9	=CHOOSE(WEEKDAY(W8,1),"S","M","T","W","T","F","S")
X8	=WORKDAY.INTL(W8,1,2)
X9	=CHOOSE(WEEKDAY(X8,1),"S","M","T","W","T","F","S")
Y8	=WORKDAY.INTL(X8,1,3)
Y9	=CHOOSE(WEEKDAY(Y8,1),"S","M","T","W","T","F","S")
AA8	=WORKDAY.INTL(Z8,1)
AA9	=CHOOSE(WEEKDAY(AA8,1),"S","M","T","W","T","F","S")
AB8	=WORKDAY.INTL(AA8,1)
AB9	=CHOOSE(WEEKDAY(AB8,1),"S","M","T","W","T","F","S")
AC8	=WORKDAY.INTL(AB8,1)
AC9	=CHOOSE(WEEKDAY(AC8,1),"S","M","T","W","T","F","S")
AD8	=WORKDAY.INTL(AC8,1)
AD9	=CHOOSE(WEEKDAY(AD8,1),"S","M","T","W","T","F","S")
AE8	=WORKDAY.INTL(AD8,1,2)
AE9	=CHOOSE(WEEKDAY(AE8,1),"S","M","T","W","T","F","S")
AF8	=WORKDAY.INTL(AE8,1,3)
AF9	=CHOOSE(WEEKDAY(AF8,1),"S","M","T","W","T","F","S")

 

[Mrlewis]

Its doesn't appear to be showing everything.

How do I share the file?

I'm using excel 2016.

 

[Joe4]

You would need to upload it to some file sharing site (like boxnet or something like that), and then provide a link here to the file download.

 

[Mrlewis]

https://www.plustransfer.com/download.php?id=2cfbd2ee83a500bacf5aae398d177c01&email=%7F%C2%83%C2%85%7B%C2%82%7Fw%C2%86%C2%87%C2%82v%7ESz%C2%80t%7C%7FAv%C2%82%C2%80

https://www.plustransfer.com/download.php?id=2cfbd2ee83a500bacf5aae398d177c01