+ Reply to Thread
Results 1 to 2 of 2

What's expiring vs current

  1. #1
    Registered User
    Join Date
    03-29-2018
    Location
    Midlothian, VA
    MS-Off Ver
    2010
    Posts
    1

    What's expiring vs current

    I'm trying to do a conditional format for license expiration dates. If it is expired (past today's date), it needs to be red. If it is expiring within the next 90 days, yellow, and good for 90 or more days, green. Any help would be appreciated.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: What's expiring vs current

    Hello and welcome to the forum.

    This will require 3 conditional formatting formulas (one for each color).

    Let's say that the license expiration days are in column A starting in A2. Apply the following rules in this order:

    Green
    Highlight A2:A100 (or however far your data goes) > Conditional Formatting > New Rule > Use a formula
    =A2-TODAY()>=90
    Format: Fill green > OK > OK

    Yellow
    Highlight A2:A100 (or however far your data goes) > Conditional Formatting > New Rule > Use a formula
    =A2-TODAY()<90
    Format: Fill yellow > OK > OK

    Red
    Highlight A2:A100 (or however far your data goes) > Conditional Formatting > New Rule > Use a formula
    =TODAY()>A2
    Format: Fill red > OK > OK

    If there can be blank cells in the column, you can use this as the red formula:
    =AND(TODAY()>A2,A2<>"")

    and this for yellow:
    =AND(A2-TODAY()<90,A2<>"")
    Last edited by 63falcondude; 03-29-2018 at 12:59 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to email on upcoming expiring dates
    By tflores626 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-19-2016, 09:40 AM
  2. Expiring credit calculation
    By kendojoe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-26-2015, 06:47 AM
  3. [SOLVED] Date Expiring Soon
    By Kheiron in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2014, 12:51 PM
  4. [SOLVED] Set PivotTable Filter to Current Day, Current Week, Current Month, or Current Year
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2014, 08:31 AM
  5. Dates expiring in 30, 60 and 90 days
    By snshyne52 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-18-2013, 07:27 AM
  6. Expiring stock projection
    By Albatroz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2012, 02:56 PM
  7. Expiring
    By Travis King in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-04-2006, 05:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1