Results 1 to 5 of 5

Thread: Conditional Formatting for dates

  1. #1

    Conditional Formatting for dates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there, I have set up a spreadsheet showing when our staff completed certain training courses. Some courses are only valid for 1 year, some 3 years, some 5 years etc. I would like to use conditional formatting to highlight if the dates are coming up to expiry (within 4 months of today goes yellow), are very near expiry (within 2 months of today goes orange) or have expired (goes red), and also to ignore if there is no date, as not all courses are relevant to all staff.

    Would appreciate any help!

    Thanks
    Attached Files Attached Files

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    853
    Articles
    0
    Excel Version
    Excel 2013

    highlight expiry date

    Quote Originally Posted by sarahbravo View Post
    and also to ignore if there is no date, as not all courses are relevant to all staff.
    Try formulas
    Red
    Code:
    =IF(AND(ISNUMBER(E5);E5<=TODAY());TRUE;FALSE)
    Orange
    Code:
    =IF(AND(ISNUMBER(E5);E5-90<=TODAY());TRUE;FALSE)
    Yellow
    Code:
    =IF(AND(ISNUMBER(E5);E5-150<=TODAY());TRUE;FALSE)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    Quote Originally Posted by navic View Post
    Try formulas
    Red
    Code:
    =IF(AND(ISNUMBER(E5);E5<=TODAY());TRUE;FALSE)
    Orange
    Code:
    =IF(AND(ISNUMBER(E5);E5-90<=TODAY());TRUE;FALSE)
    Yellow
    Code:
    =IF(AND(ISNUMBER(E5);E5-150<=TODAY());TRUE;FALSE)
    -----------------------------------------------------------------------
    This is good, except that the OP's dates are not the expiration dates!
    I'd suggest the OP use EXPIRATION date, rather than TESTING date. If he does, the above procedure should work.

    Hope this helps!

  4. #4
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,601
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @candybg

    Please don't quote entire posts. They clutter the thread and make it hard to read.

    @sarah

    what are those dates? When is the expiration date ? PLease be more specific

    @ navic

    CF does not need the IF function nor the TRUE/FALSE usually. A condition like

    =AND(ISNUMBER(E5);E5-90<=TODAY()) is perfectly valid ( I'm not saying it is the correct answer to the OP's query, though)
    Last edited by Pecoflyer; 2016-03-18 at 07:08 PM.

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    853
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Pecoflyer View Post
    @ navic
    CF does not need the IF function nor the TRUE/FALSE usually.
    I know that.
    But! @sarahbravo will better understand the essence how work of formula.

    here is my example for @sarahbravo
    Attached Files Attached Files
    Last edited by navic; 2016-03-18 at 09:32 PM.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •