Share via

Conditional Formatting to Show Expiration

Anonymous
2014-04-29T18:36:23+00:00

I have searched the archives and thought I had found an answer but it doesn't seem to be working so I am reaching out for help.  I am trying to set up a spreadsheet that I have that shows the expiration dates of insurance certificates. I would like to create an icon based conditional formatting that shows the following:

Green = Current

Yellow = over 30 days expired

Red = over 6 months expired

Black = over 1 year expired

HELP!!! PLEASE! I can't seem to figure it out.

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2014-05-01T07:35:51+00:00

    Hi,

    select the desired range e.g. B2:E7

    and add the following rules

    Green dot = Current (date is greater than today)

    =AND(B2<>"",B2>TODAY())

    Yellow dot = up to 30 days expired  (date is before today up to or equal to 30 days)

    =AND(B2<>"",B2<=TODAY()-1,B2>=EDATE(TODAY(),-1))

    Red dot = up to 12 months expired (date is before today by more than 30 days up to 12 months)

    =AND(B2<>"",B2>=EDATE(TODAY(),-12),B2<EDATE(TODAY(),-1))

    Black dot = over 1 year expired (date is before today by more than 12 months/1 year)

    =AND(B2<>"",B2<EDATE(TODAY(),-12))

    instead of black is blue

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2014-05-08T18:29:02+00:00

    Hi,

    1. first top left cell is C2 and not B2

    also...

    A)

    B)

    0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2014-04-29T23:35:52+00:00

    Hi,

    Try this

    1. Suppose your data is in range A1:D8.  Headings are in A1:D1
    2. Click on cell A2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
    3. In the Formula box there, enter this formula

    =(A2-TODAY())<30

    1. Click on Format > Fill > Green > OK > OK
    2. Repeat step 2 and in step 3, type the following formula

    =AND((A2-TODAY())>30,TODAY()<EDATE(A2,6))

    1. Click on Format > Fill > Yellow > OK > OK
    2. Repeat step 2 and in step 3, type the following formula

    =AND((A2-TODAY())>30,TODAY()<EDATE(A2,12))

    1. Click on Format > Red > Yellow > OK > OK
    2. Repeat step 2 and in step 3, type the following formula

    EDATE(A2,12)>TODAY()

    1. Click on Format > Red > Black > OK > OK
    2. Copy cell A2, select A2;D8 > right click > Paste Special > Formats > OK

    Hope this helps.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-04-29T18:51:51+00:00
    Liability Expiration Automobile Expiration Workers Comp Expiration Professional Liability
    07/09/14 07/09/14 07/21/14
    10/07/13 05/01/14 10/07/13
    09/24/13 09/24/13 01/01/14 09/23/13
    04/01/15 04/01/15 10/01/14 04/01/15
    05/01/15 05/01/15 09/01/14

    Sorry, should've been more specific.  This is what it looks like. I would just like to add the icons to the individual cells based on whether the expiration date has passed by 30 days, 6 months or more than a year.  Anything current would be in green.  But all of the cells in the spreadsheet have different expiration dates depending on the vendor.

    Does that help?

    0 comments
  3. Anonymous
    2014-04-29T18:46:45+00:00

    Hi, assuming you have the days calculation in column D and you want to color column A position in A4 for example then go to Conditional formatting, new rule, use the formula (last option(, there enter

    =$D$4<60 go to format, fill choose color green OK OK, do it again but entering this formula

    =AND($D$4>=60,$D$4<180) choose color yellow then for red use

    =AND(D$$4>=180,$D$4<365) and for black

    =$D$4>=365

    0 comments