Forum Discussion

spalmer's avatar
spalmer
Iron Contributor
Jun 04, 2021
Solved

formula and formatting help

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 d...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 04, 2021

    spalmer 

    Change the formula to

     

    =IF(COUNTBLANK(Table1[@[INSTR. QUAL. TYPE / LEVEL:]:[INSTR. QUAL. EXP. DATE:]]),"",IF([@[INSTR. QUAL. EXP. DATE:]]<TODAY(),"Expired","Active"))

spalmer's avatar
spalmer
Iron Contributor
Jun 04, 2021
Thank you Hans! would you happen to know how to fix that conditional formatting issue im having as well?
HansVogelaar's avatar
HansVogelaar
MVP
Jun 04, 2021

spalmer 

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.

 

  • spalmer's avatar
    spalmer
    Iron Contributor
    Jun 08, 2021

    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

      • spalmer's avatar
        spalmer
        Iron Contributor
        Jun 08, 2021
        ya I have tried flipping the order but still didn't work.
  • spalmer's avatar
    spalmer
    Iron Contributor
    Jun 05, 2021
    you are awesome Hans! thank you so much really appreciate your help and time

Resources