Conditional Formatting based on other cell values

[nicolews]

Hello. I am unable to find the proper conditional formatting custom formula for the below. Help!

I have a tracker file that shows project milestone due dates and their day count down to those due dates in the next column. The day count down is auto calculated each time you open the file- I’m good there.
Ex: Row 1 shows Project description, milestone #1 due date, milestone #1 day count down, milestone #2 due date, milestone #2 day count down, and so on through milestone #20 due date and it’s day countdown.

What I’m wanting is to add a column before milestone #1 due date that will show me if any of the milestones are approaching (since it’s a lot to scroll continuously to the right and view the due dates/day counters that are already rendered warning colors based on their individual values).

Wanting the new column to display the below if any of the milestones are in range- thinking of using each day count down, but not married to that idea.
- “Incoming” w/ cell filled yellow: due in 8-15 days
- “Due” cell filled red: due in 0-7 days
- “Past Due” cell filled red w/red bold font: past due date (or a negative day count number)

I understand how to format the new cell based on the value of a different cell… what is tripping me up is how to look for that value across a row and/or in alternating cells in that row. Does that make sense?

 

[dalvin647]

Hi Nicolews,

Try this

Book2
	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
3	Project Detail	MileStone Approaching?	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2	MileStone1 Due Date	Day Ticker	MileStone2 Due Date	Day Ticker2
4			2023-06-20	7	2023-06-22	9
5			2023-07-01	18	2023-06-12	-1
6			2023-08-01	49	2023-06-25	12
Sheet1

Cell Formulas
Range	Formula
D4:D6,F4:F6	D4	=C4-TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B6	Expression	=MIN(C4:AL4)<0	text	NO
B4:B6	Expression	=MIN(C4:AL4)<=7	text	NO
B4:B6	Expression	=MIN(C4:AL4)<=15	text	NO
F4:F6	Cell Value	between 0 and 7	text	NO
F4:F6	Cell Value	between 8 and 15	text	NO
F4:F6	Cell Value	<0	text	NO
F4:F6	Cell Value	between 8 and 15	text	NO
D4:D6	Cell Value	between 0 and 7	text	NO
D4:D6	Cell Value	between 8 and 15	text	NO
D4:D6	Cell Value	<0	text	NO
D4:D6	Cell Value	between 8 and 15	text	NO

 

[nicolews]

Hi there! I was not able to make that work. As this file is on my work computer, I am unable to download the mini-sheet program in order to upload the sample file I am working in.

Thank you for your time- if you have any other suggestions, I would greatly appreciate them!

 

[ERINWILLIS01]

HI! I just did something similar with an invoice tracking sheet. So for due dates within 8-15 days, highlight you milestone 1 due date cells for your range, then choose to highlight cells with a cell value between =today()+8 and =today()+15, then choose the format with yellow fill and dark yellow text.
Then do the same with the due in 0-7 days changing the formula to =today() and today()+7, then format it to just the red fill.
And for the past due, same thing except you will choose highlight cells with cell value less than =today(). Then chose the red fill and red text.

Hope that helps!