# Conditional formatting formula

#### Mrlewis

##### New Member
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?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Re: Help needed with a formula in conditional formatting

Welcome to the Board!

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.

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.

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
1
2
4
5Jan-4Jan-11Jan-18Jan-25Feb-1Feb-8
6PROJECT START DATE31-Dec-18SCROLL TO WEEK #1
7
8MachiningPriorityAction OwnerStartFinishDurationStatus% Complete311234567891011121314151617181920212223242526272829303112345678910
9MTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSS
10Project Summary01-Jan-1903-Mar-1944 day(s)93%
11Project 101-Jan-1903-Mar-1962 day(s)100%
Project Tracker
Cell Formulas
RangeFormula
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")
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)

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.

Last edited:
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.

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

It looks like J11 is actually a formula:
Code:
``=[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.

Last edited:
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.

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.

Last edited:

Replies
3
Views
648
Replies
2
Views
134
Replies
7
Views
222
Replies
2
Views
419
Replies
9
Views
145

1,207,402
Messages
6,078,265
Members
446,324
Latest member
JKamlet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back