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.
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 |