Jun 04 2021 10:56 AM - edited Jun 08 2021 09:35 AM
Hey everyone, so I need help with a formula. In the sheet attached I have "INSTR QUAL STATUS" cell (F15) and I tried using a IF AND Function but couldn't get it to work for me. what I'm trying to do is have it say Active or Expired but only if there is data entered in either B16-E16 cell. if there is no data entered in any of those cells I want F16 to be blank.
Also you will see some conditional formatting in cell E16. I want the conditional formatting to work only if there is data entered in it. if its blank I want it to remain white.
thank you for any and all help.
Jun 04 2021 11:34 AM
SolutionChange the formula to
=IF(COUNTBLANK(Table1[@[INSTR. QUAL. TYPE / LEVEL:]:[INSTR. QUAL. EXP. DATE:]]),"",IF([@[INSTR. QUAL. EXP. DATE:]]<TODAY(),"Expired","Active"))
Jun 04 2021 02:04 PM
Jun 04 2021 02:15 PM
Select the cell.
On the Home tab of the ribbon, click Conditional Formatting > Manage Rules...
Click Edit Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND(E16<>"",E16<TODAY())
Click OK, then click OK again.
Jun 04 2021 05:58 PM
Jun 08 2021 09:37 AM - edited Jun 08 2021 09:37 AM
Alright Hans, so i have another issue that doesnt seem to be working with the conditional formatting. your formula you gave me did work and i also needed another one that showed in yellow when the exp date was coming up in 140 days. i tried that formula you gave me but didnt work and i tried the current formula thats in there now and even when i delete the data it still shows up yellow. would you be able to help me out with this? i attached a new book
Jun 08 2021 09:47 AM
It's probably a matter of placing the rules in the correct order.
Jun 08 2021 10:49 AM
Jun 08 2021 11:23 AM
Did you look at the version that I attached?
Could you attach a workbook with the non-working rules? The one you attached to your previous reply was the same as the first one...
Jun 08 2021 02:38 PM - edited Jun 08 2021 02:39 PM
i apologize here it is
Jun 08 2021 02:48 PM
You used OR instead of IF in the formula for yellow. If you change OR to AND, it'll work as intended:
See the attached version.
Jun 09 2021 08:11 AM
Jun 04 2021 11:34 AM
SolutionChange the formula to
=IF(COUNTBLANK(Table1[@[INSTR. QUAL. TYPE / LEVEL:]:[INSTR. QUAL. EXP. DATE:]]),"",IF([@[INSTR. QUAL. EXP. DATE:]]<TODAY(),"Expired","Active"))