Conditional formatting with different dates

[Jdog12]

I set up a spreadsheet for final pay with conditional formatting It is to remind me when to start processing final check.

Cell D1 has the current date.

Cell D8 is last day of work

Cell F8 is 5 days prior to last day

Cell B8 has conditional formatting when current date is greater than or equal to 5 days prior to last day.

Column F thru Column I will be hidden

It works fine when I set up conditional formatting for the rows I want every time. On the row with conditional formatting, when I delete only the name and last day, the message “To process final pay “ is still there until I enter a new name and last date.

Is there a way to do this without setting up conditional formatting every time? Basically, I don’t want to see the message “To process final pay” when the row has no name nor last date.

Any help or suggestion will be appreciated.

conditional formatting for final pay.xlsx
	A	B	C	D	E	F	G	H	I
1			current date	9/30/2021				5	To process final pay
2				=TODAY()
3
4
5
6	Name	Action		Last day of work		5 days prior to last day
7
8	John Doe	To process final pay		9/28/2021		9/23/2021
9		=IF($D$1>=F8, "To process final pay", " ")				=$D$8-$H$1
10
11
12
13
14		To process final pay				####################
15
16
Sheet2

Cell Formulas
Range	Formula
D1	D1	=TODAY()
D2,F9,B9	D2	=FORMULATEXT(D1)
B8,B14	B8	=IF($D$1>=F8, "To process final pay", " ")
F8	F8	=$D$8-$H$1
F14	F14	=$D$14-$H$1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	=$I$1	text	NO

 

[etaf]

you can get rid of all those hidden columns if you want and just write 1 formula for B and 1 formula for conditional formatting

but to answer just the question you asked
add a check for column A being blank, if its NOT Blank , then you want to check the dates

we can use and AND and add A8 <> "" A8 does not equal a blank

=IF($D$1>=F8, "To process final pay", " ")
change to
=IF(AND ( A8 <> "", $D$1>=F8), "To process final pay", " ")

if you want to get rid of all those other references
we can add it directly into the formulas and the same for conditional formatting
But I will await your reply, as i have to leave now for the day ..... others may answer meanwhile

 

[Jdog12]

you can get rid of all those hidden columns if you want and just write 1 formula for B and 1 formula for conditional formatting

but to answer just the question you asked
add a check for column A being blank, if its NOT Blank , then you want to check the dates

we can use and AND and add A8 <> "" A8 does not equal a blank

=IF($D$1>=F8, "To process final pay", " ")
change to
=IF(AND ( A8 <> "", $D$1>=F8), "To process final pay", " ")

if you want to get rid of all those other references
we can add it directly into the formulas and the same for conditional formatting
But I will await your reply, as i have to leave now for the day ..... others may answer meanwhile

It did not work becasue when I removed the name from column A, "To process final pay" is still in column B.

Basically, I want to remove the name and last day of work , and "To process final pay" stop appearing. Then when I enter new info to name and last day, the conditional formatting will work automatically.

 

[Alex Blakenburg]

Did you put @etaf 's formula in B8 ?
Using row 8 I would put this in F8

=IF($D8="","",$D8-$H$1)

And this in B8 basically the same just $ in different places.

=IF(AND($A8<>"",$D$1>=$F8), "To process final pay", " ")

 

[Jdog12]

Thank etaf for leading me to the direction.

I figured it out with "AND" function

conditional formatting for final pay.xlsx
	A	B	C	D	E	F
1			current date	10/1/2021
2				=TODAY()
3
4
5
6	Name	Action		Last day of work		5 days prior to last day
7
8	John Doe	To process final pay		9/28/2021		9/23/2021
9		=IF(AND(A8<>" ",D8<>" ",$D$1>=F8,F8>0),"To process final pay", " ")				=$D$8-$H$1
10
11
Sheet2

Cell Formulas
Range	Formula
D1	D1	=TODAY()
D2,F9,B9	D2	=FORMULATEXT(D1)
B8	B8	=IF(AND(A8<>" ",D8<>" ",$D$1>=F8,F8>0),"To process final pay", " ")
F8	F8	=$D$8-$H$1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B7,B9:B1048576	Cell Value	=$I$1	text	NO

 

[etaf]

glad its all sorted for you now

As i say , you don't actually need all the other columns , and can do it all with just 1 formula , unless you need the columns for some other reason