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"))

HansVogelaar's avatar
HansVogelaar
MVP
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

Resources