Conditional formatting based on value of another cell

[jerylupe]

Hello all and thank you kindly for any help that might be provided. I have a large excel that is going to continue to grow to which this will be applied / added, so the formula has to be indefinite and be able to be easily added to an already ongoing file.

I am trying to format column C (Due Date) to provide a "warning" based on the value of column X (Closed Date). If there is data in Column X, I do not need to conditional format C as the review is complete and we do not need any warning indicators of approaching due dates. Currently, I am just utilizing the conditional formatting option on the home tab for all of column C, however, as the formatting remains even on cases / reviews that are closed, it is not an effective warning notification of cases still open, but coming due soon.

Color does not really matter that much, but I have been using Red/Red for cases / reviews due in the same (this) week and Yellow/Yellow for cases coming due in the next week.

Something like =if(X<>" ", no conditioning needed , condition C " is the basic intent here.

Then there is one more layer which can be left out if it makes this overly complicated, but technically, the first approaching Sunday is considered as part of "this week", so any case reviews that are due on Sunday are technically part of the prior week as they have to be closed by the preceding Friday.
Upload Image
Any help here would be greatly appreciated!

 

[etaf]

a couple of questions
you want to format column C based on case reviews that have not had a date entered
So all Blank cells in column C
BUT where is the date to reference ????
what column should we test to see how old - ie Same week and nextweek

you should be able to USE
AND ()

AND( Cell that contains the date <> "" , Column C cell ="", Cell that contains the date <=today() formula for this week ) - format fill - RED
AND( Cell that contains the date <> "" , Column C cell ="", Cell that contains the date <=today() formula for next week ) - format fill - Yellow

Two rules for conditional formatting

you can work out this week Sunday date
=(7-WEEKDAY(A1,2))+A1
and next week sunday date
=(14-WEEKDAY(A1,2))+A1

so need a little more info

What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available.
Excel Versions are: 2003/2007/2010/2013/2016/2019/2021/2024 (due April24) /365 subscription , also rather than show more than 1 version . if you have them , show which version you will be using the solution for.
An awful lot of new functions have now been added to the newer versions

 

[jerylupe]

I will try to apply the above and play around with it later when I have some free time. I am not very proficient with formulas and code, but have some knowledge and knowhow on it... but to answer your questions in case that changes anything (which it appears to):

Regarding the version: I am on Office 365, hopefully there are not sub versions within O365 or even variations, but that is what we are currently on in the office in which this is to be applied.

Column C will be the case review due date, and IF the "closed date" AKA column X is already filled in, there is no formatting of the due date needed as the review is fully complete and therefore, there is no need to indicate an upcoming SLA / Due date approaching. I would love to upload an image for a visual, but I am signed in here on my personal machine and I cannot send emails externally for security reasons and this will be for work.

So if X has a date, C should appear without any type of formatting... However, if X is still blank and the due date is this week, I have been using Light Red Fill with Dark Red Text, and if due the next week, I was using Yellow fill with Dark Yellow Text, but whatever works best.

I was just using "this" and "next" as that was the two easiest options from in the Conditional Formatting choices already given, but anything works really as long as I get a clear "alert" of sorts for cases coming due and still not fully closed. So whatever is easiest...

Not sure if it is easier to say if X is blank and C is within the next 7 days from today, give it one result (preferably a real attention grabber - Red seems to stand out), or if due from 8 - 14 days from now, give it another result (possibly the yellow).

Really this is just a warning indicator for case reviews pending that are open, but coming due soon. Sorry for the novel as well!!!

 

[etaf]

Ok, so 2 formulas I think should work are

next week
=AND(C2<>"",X2="",C2<=(14-WEEKDAY(TODAY(),2))+TODAY(), C2>(7-WEEKDAY(TODAY(),2))+TODAY(),C2>=TODAY())

This week
=AND(C2<>"",X2="",C2<=(7-WEEKDAY(TODAY(),2))+TODAY(),C2>=TODAY())

you can apply to a large range of rows - as i have used C2<>"" , so will not highlight if C2 is blank

column M and N are not necessary - BUT i jjust wanted to show the formula working - giving TRUE or FALSE - so you can see the logic

for it to colour X2 must be blank
X2=""


now we test to see if greater than today and less than next week sunday, THIS WEEK - if TRUE will colour providing C2 is not blank and x2 is blank
C2<=(7-WEEKDAY(TODAY(),2))+TODAY(),C2>=TODAY())

But what do you want to show if the date is past???
perhaps another rule - showing deep red and white text or black and white .....

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
1													nextweek	tjisweek
2			3/22/24										FALSE	FALSE		=AND(C2<>"",X2="",C2<=(14-WEEKDAY(TODAY(),2))+TODAY(), C2>(7-WEEKDAY(TODAY(),2))+TODAY(),C2>=TODAY())
3			3/23/24										FALSE	FALSE
4			3/24/24										FALSE	FALSE		=AND(C4<>"",X4="",C4<=(7-WEEKDAY(TODAY(),2))+TODAY(),C4>=TODAY())								1/1/24
5			3/25/24										FALSE	TRUE
6			3/26/24										FALSE	TRUE
7			3/27/24										FALSE	TRUE
8			3/28/24										FALSE	FALSE										1/1/24
9			3/29/24										FALSE	TRUE
10			3/30/24										FALSE	TRUE
11			3/31/24										FALSE	TRUE
12			4/1/24										TRUE	FALSE
13			4/2/24										FALSE	FALSE										1/1/24
14			4/3/24										FALSE	FALSE										1/1/24
15			4/4/24										TRUE	FALSE
16			4/5/24										TRUE	FALSE
17			4/6/24										TRUE	FALSE
18			4/7/24										TRUE	FALSE
19			4/8/24										FALSE	FALSE
20			4/9/24										FALSE	FALSE										1/1/24
21			4/10/24										FALSE	FALSE
22			4/11/24										FALSE	FALSE
23			4/12/24										FALSE	FALSE
24			4/13/24										FALSE	FALSE
25			4/14/24										FALSE	FALSE
26			4/15/24										FALSE	FALSE
27			4/16/24										FALSE	FALSE
28			4/17/24										FALSE	FALSE
29			4/18/24										FALSE	FALSE
30			4/19/24										FALSE	FALSE
Sheet1 (2)

Cell Formulas
Range	Formula
M2	M2	=AND(C2<>"",X2="",C2<=(14-WEEKDAY(TODAY(),2))+TODAY(), C2>(7-WEEKDAY(TODAY(),2))+TODAY(),C2>=TODAY())
N2:N30	N2	=AND(C2<>"",X2="",C2<=(7-WEEKDAY(TODAY(),2))+TODAY(),C2>=TODAY())
M3:M30	M3	=AND(X3="",C3<=(14-WEEKDAY(TODAY(),2))+TODAY(), C3>(7-WEEKDAY(TODAY(),2))+TODAY(),C3>=TODAY())
P2	P2	=FORMULATEXT(M2)
P4	P4	=FORMULATEXT(N4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C33	Expression	=AND(C2<>"",X2="",C2<=(14-WEEKDAY(TODAY(),2))+TODAY(), C2>(7-WEEKDAY(TODAY(),2))+TODAY(),C2>=TODAY())	text	NO
C2:C133	Expression	=AND(C2<>"",X2="",C2<=(7-WEEKDAY(TODAY(),2))+TODAY(),C2>=TODAY())	text	NO