"IF" formula regarding a 3rd option, and automatic font color changes.

  • Background: I have a spreadsheet and I'm using a simple formula (=IF(K5>J5,"Expired","Valid").
    K5 is an expiration date, J5 uses the formula =Today().
    If todays date is greater than the expiration date then the status should be Expired otherwise status will be Valid.


    I have two questions:
    1. not all entries will have an expiration date, so how can I add into the formula if the K5 is null display N/A?


    2. Is it possible to have the font color change based on the status? (example: I'd like to have all Valid status appear in green, and all Expired appear in red.

  • Hi slick225,


    You can nest your IF statements to test the contents of K5.


    =IF(K5="","",IF(K5<J5,"Expired","Valid"))


    By the way the test was the wrong way round according to your description.


    The colour changes can be applied using conditional formating.
    Use;
    CellIs Equalto ="Expired"
    CellIs Equalto ="Valid"


    format the cell and or font as required.

    [h4]Cheers
    Andy
    [/h4]

  • Andy thanks, the color format is now in place, but I'm having some issues with the formula.


    More detail on the layout


    Cell........I........................J.........................K
    2...........Status................Expire Date...........Todays Date
    3...........Expired..........................................9/16/2004
    4...........Valid..................1/28/05.................9/16/2004


    Intent: when todays date is greater than the expire date display the text Expired otherwise display Valid, but if there is no Expire Date display the text N/A.


    Formulas:
    Original formula for Cell I:3 - =IF(K3>J3,"Expired","Valid")
    Results in cell I = Expired (I want this to display - N/A)


    Original formula for Cell I:4 - =IF(K4>J4,"Expired","Valid")
    Results in cell I = Valid (this is correct b/c the Expire date is greater than todays date.)


    I tried what you suggested: in the I:3 cell - =IF(K3="","",IF(K3>J3,"Expired","Valid"))
    Results in cell I = Expired (there was no change, did I need to add arguments?)

  • If the value in column K is always going to be Today's Date, then why not incorporate Today() into the formula in column I.


    EDIT: =IF(ISBLANK(J3),"N/A",IF(TODAY()>J3,"Expired","Valid"))


    If cell in column J is blank, then show "N/A", otherwise show either "Valid" or "Expired".


    Conditional Formatting is applied to the formula in I3 then copy the formula down.


    See attached example.

  • That did the trick Barbarr and it also simplified the process by eliminating an un-needed column (Today's Date).


    Thanks for all your help (Andy P, and Barbarr)

  • You're welcome. Glad it was what you needed. :)

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!