Conditional Formatting using finish date and % complete

[mln5446]

Hello...
I have read multiple threads and tried several different formulas mentioned to conditionally format worksheet but none are doing it right on all the cells

I would like to format column F based on the finish date being greater, less or equal to Today, and using the % complete in column F. Below is what i was trying to do and the formula I attempted and the result

1. If the finish date is within two weeks of Today and % complete less than 100% I would like amber =IF(AND(E16>TODAY()+14,F16<0.9),1,0)
2. If the finish date is greater than two weeks from today and % complete less than 100% green =IF(AND(E16>TODAY()+"",F16<0.9),1,0)
3. If the finish date is earlier (i.e. late) than today and % complete less than 100% red =IF(AND(E16<TODAY(),F16<1),1,0)
4. If the finish date is earlier than today and % complete 100% green =IF(AND(E16<TODAY(),F16=1),1,0)


For the Amber in one instance due date is the 7th of Aug and today is the 7th of July. I should not get amber, but should get Green using 2. above.

If better to use Now vs Today and or I have something placed wrong would appreciate the help.

 

[StephenCrump]

Welcome to the Forum!

Is this closer to what you're looking for:

1. Format GREEN
2. Conditional format RED, using formula: =AND(E16<=TODAY(),F16<1)
3. Conditional format AMBER, using formula: =AND(E16>TODAY(),E16<=TODAY()+14,F16<1)

 

[Toadstool]

Hi Min5446,

Is this how you want the rules expressed?

Min5446.xlsx
	E	F
15	Finish	% Complete
16	07-Jul-21	0%
17	06-Jul-21	0%
18	01-Jun-21	100%
19	06-Jul-21	0%
20	07-Aug-21	0%
21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F16:F9999	Expression	=IF(AND(E16<>"",E16<TODAY(),ROUND(F16,2)=1),1,0)	text	NO
F16:F9999	Expression	=IF(AND(E16<>"",E16<TODAY(),F16<1),1,0)	text	NO
F16:F9999	Expression	=IF(AND(E16<>"",E16>TODAY()+14,F16<1),1,0)	text	NO
F16:F9999	Expression	=IF(AND(E16<>"",E16>=TODAY(),E16<=TODAY()+14,F16<1),1,0)	text	NO

 

[mln5446]

Good Day

Hi Min5446,

Is this how you want the rules expressed?

Min5446.xlsx
	E	F
15	Finish	% Complete
16	07-Jul-21	0%
17	06-Jul-21	0%
18	01-Jun-21	100%
19	06-Jul-21	0%
20	07-Aug-21	0%
21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F16:F9999	Expression	=IF(AND(E16<>"",E16<TODAY(),ROUND(F16,2)=1),1,0)	text	NO
F16:F9999	Expression	=IF(AND(E16<>"",E16<TODAY(),F16<1),1,0)	text	NO
F16:F9999	Expression	=IF(AND(E16<>"",E16>TODAY()+14,F16<1),1,0)	text	NO
F16:F9999	Expression	=IF(AND(E16<>"",E16>=TODAY(),E16<=TODAY()+14,F16<1),1,0)	text	NO

Good Day. Thank you for the support. As you presented it that is how I would like it to appear, however, I still am having some cells not reflect the conditions. I am checking the date formats as it seems like this may have impact, although not sure exactly why.

Thank you

 

[Toadstool]

I am checking the date formats as it seems like this may have impact, although not sure exactly why.

There can be a few challenges with dates:

1. My PC is from the USA so interprets 1/6/2021 as 6th January 2021, mm/dd/yyyy. Yours may differ if you used my data example and your PC uses dd/mm/yyyy.
2. It may be your dates are text containing some artifacts preventing calculations. Find an unused cell and enter =E16+1 and it should give the date of the day after that shown in E16.
3. Select a date cell and check if it show hours and minutes. If it does then calculations may be off by one day as TODAY() gives today's date at 0 hours and 0 minutes.