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?

 

[Joe4]

Re: Help needed with a formula in conditional formatting

Welcome to the Board!

I think we are going to need more information than that in order to be able to help you.
Please post your Conditional Formatting formulas, and the relevant data.

If is easier to show an image of the relevant data, there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[Mrlewis]

Re: Help needed with a formula in conditional formatting

Thank you, I will post further details tomorrow as I can't do it from my phone currently.

 

[Mrlewis]

Re: Help needed with a formula in conditional formatting

Hi,

Details as follows:

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

Cell values:
J11 is 100%
F11 is 01-Jan-19
G11 is 03-Mar-19

Book1
	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	AT	AU	AV	AW	AX	AY	AZ	BA
1
2
3													Task in Progress								Task Completed
4
5												Jan-4							Jan-11							Jan-18							Jan-25							Feb-1							Feb-8
6			PROJECT START DATE	31-Dec-18			SCROLL TO WEEK #		1
7
8			Machining	Priority	Action Owner	Start	Finish	Duration	Status	% Complete		31	1	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	6	7	8	9	10
9												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	M	T	W	T	F	S	S
10			Project Summary			01-Jan-19	03-Mar-19	44 day(s)		93%
11			Project 1			01-Jan-19	03-Mar-19	62 day(s)		100%
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")
AG5	=Z5+7
AG8	=WORKDAY.INTL(AF8,1)
AG9	=CHOOSE(WEEKDAY(AG8,1),"S","M","T","W","T","F","S")
AN5	=AG5+7
AN8	=WORKDAY.INTL(AM8,1)
AN9	=CHOOSE(WEEKDAY(AN8,1),"S","M","T","W","T","F","S")
AU5	=AN5+7
AU8	=WORKDAY.INTL(AT8,1)
AU9	=CHOOSE(WEEKDAY(AU8,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")
AH8	=WORKDAY.INTL(AG8,1)
AH9	=CHOOSE(WEEKDAY(AH8,1),"S","M","T","W","T","F","S")
AI8	=WORKDAY.INTL(AH8,1)
AI9	=CHOOSE(WEEKDAY(AI8,1),"S","M","T","W","T","F","S")
AJ8	=WORKDAY.INTL(AI8,1)
AJ9	=CHOOSE(WEEKDAY(AJ8,1),"S","M","T","W","T","F","S")
AK8	=WORKDAY.INTL(AJ8,1)
AK9	=CHOOSE(WEEKDAY(AK8,1),"S","M","T","W","T","F","S")
AL8	=WORKDAY.INTL(AK8,1,2)
AL9	=CHOOSE(WEEKDAY(AL8,1),"S","M","T","W","T","F","S")
AM8	=WORKDAY.INTL(AL8,1,3)
AM9	=CHOOSE(WEEKDAY(AM8,1),"S","M","T","W","T","F","S")
AO8	=WORKDAY.INTL(AN8,1)
AO9	=CHOOSE(WEEKDAY(AO8,1),"S","M","T","W","T","F","S")
AP8	=WORKDAY.INTL(AO8,1)
AP9	=CHOOSE(WEEKDAY(AP8,1),"S","M","T","W","T","F","S")
AQ8	=WORKDAY.INTL(AP8,1)
AQ9	=CHOOSE(WEEKDAY(AQ8,1),"S","M","T","W","T","F","S")
AR8	=WORKDAY.INTL(AQ8,1)
AR9	=CHOOSE(WEEKDAY(AR8,1),"S","M","T","W","T","F","S")
AS8	=WORKDAY.INTL(AR8,1,2)
AS9	=CHOOSE(WEEKDAY(AS8,1),"S","M","T","W","T","F","S")
AT8	=WORKDAY.INTL(AS8,1,3)
AT9	=CHOOSE(WEEKDAY(AT8,1),"S","M","T","W","T","F","S")
AV8	=WORKDAY.INTL(AU8,1)
AV9	=CHOOSE(WEEKDAY(AV8,1),"S","M","T","W","T","F","S")
AW8	=WORKDAY.INTL(AV8,1)
AW9	=CHOOSE(WEEKDAY(AW8,1),"S","M","T","W","T","F","S")
AX8	=WORKDAY.INTL(AW8,1)
AX9	=CHOOSE(WEEKDAY(AX8,1),"S","M","T","W","T","F","S")
AY8	=WORKDAY.INTL(AX8,1)
AY9	=CHOOSE(WEEKDAY(AY8,1),"S","M","T","W","T","F","S")
AZ8	=WORKDAY.INTL(AY8,1,2)
AZ9	=CHOOSE(WEEKDAY(AZ8,1),"S","M","T","W","T","F","S")
BA8	=WORKDAY.INTL(AZ8,1,3)
BA9	=CHOOSE(WEEKDAY(BA8,1),"S","M","T","W","T","F","S")
F10	=IF(MIN(F11:F31)>0,MIN(F11:F31),"")
F11	=IF(MIN(F12:F16)>0,MIN(F12:F16),"")
G10	=IF(MAX(G11:G31)>0,MAX(G11:G31),"")
G11	=IF(MAX(G12:G15)>0,MAX(G12:G16),"")
H10	=IF(OR(F10="",G10=""),"",NETWORKDAYS(F10,G10)& " day(s)")
H11	=DAYS(G11,F11)+1&" day(s)"
J10	=AVERAGE(J12:J30)
J11	=AVERAGE(J12:J15)

 

[Joe4]

Re: Help needed with a formula in conditional formatting

In your formula, what exactly are you trying to do with this piece:
$F11<=L$8

Note that F11 is a date (01-Jan-19) and L8 is a number (31). Dates in Excel are actually stored as number, specifically the number of days since 1/0/1900.
If you temporarily change the format of your date to "General", you will see that its numerical value is 43466. Since that number is greater than 31, that part of your formula will evaluate to FALSE.
As matter as fact, any date enter in F11 that is after the year 1901 will always cause that part to return false.

Are you trying to compare the DAY part of your date? If so, change that part of the formula to:
DAY($F11)<=L$8

And be sure to make that change anywhere else you are trying to make the same type of comparison.

 

[Mrlewis]

Hi,
I'm trying to highlight cells via the formula in conditional formatting to show progress on each day to the end date through a project depending on the percentage applied in J11.
It only seems to work (shows completion of the date) when 130% is input into this.
I have changed the formula but it highlights L11 which isn't always the start date of each task within the project.

 

[Mrlewis]

The formula is applied to cells L11 to BA11 if that helps

 

[Joe4]

It looks like J11 is actually a formula:

=[COLOR=#000000][FONT=Calibri]AVERAGE([/FONT][/COLOR][COLOR=Blue][FONT=Calibri]J12:J15[/FONT][/COLOR][COLOR=#000000][FONT=Calibri])[/FONT][/COLOR]

What values do you have in J12:J15?
I am trying to make a setup of your copy on my side to see if I can reproduce the behavior you are experiencing.

Also, which version of Excel are you using?

Also, please post the current function (since you made the changes), and explain what each part is supposed to be doing.
I do not want to assume the logic you are using based on the formula, in case there are errors in the formula.

 

[Mrlewis]

J12:J15 are all at 100%
They have a conditional formatting formula that highlights / colours a percentage of that cell depending on what percentage its at. I'll get back to you on the version of excel I'm using.
Thank you for your efforts.

 

[Joe4]

OK, please also be sure to post the most current version of your formula, after the changes were made.
Also, it looks like it is an AND function with three different arguments.
The first argument is easy enough ($J11>5%), it is checking to see if J11 is greater than 5%.
Please explain exactly what the other two arguments in your formula are supposed to be doing.