Solution to Problem: Conditional Formatting to Show Expiration

Conditional Formatting to Show Expiration

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!

Anwsers to the Problem Conditional Formatting to Show Expiration

 

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),B2Black dot = over 1 year expired (date is before today by more than 12 months/1 year)
=AND(B2<>"",B2

instead of black is blue

Run CHKDSK

• Go to the Start button.
• In the Search box, type "cmd" (without quotes) and press Enter.
• At the command prompt, type "chkdsk /r /f" (without quotes) and press Enter.
• Reboot your computer.

NOTE: You might receive an error message that states:

"Access denied as you do not have significant privileges. You have to invoke this utility in elevated mode."

If you receive the above error message, it means you are not logged into you computer as an Administrator. If that's the case, follow these steps to run the command prompt in elevated mode:

• Go to theStart button.
• Go to "All Programs > Accessories" and right click on "Command Prompt".
• Select "Run as Administrator".
• In the Search box, type "cmd" (without quotes) and press Enter.
• At the command prompt, type "chkdsk /r /f" (without quotes) and press Enter.
• Reboot your computer.

Recommended Method to Fix the Problem: Conditional Formatting to Show Expiration:

 

How to Fix Conditional Formatting to Show Expiration with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer.  Open it, and it will perform a scan for your computer. The errors will be shown in the list.

2. After the scan is done, you can see the errors and problems need to be fixed. Click Fix All.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in SmartPCFixer. Like dll downloading, junk file cleaning and print spooler error repair.

Related: Strange unknown programs in the Notification Area Icons list Using Countifs to match data if cells are not blank Sysax Multi Server cannot be started Webcam and Audio Driver not recognized after upgrade from Vista to Windows 7 The file cannot be opened because there are problems with the contents Details The name in the end tag must match the element type in the start tag the ordinal 49 could not be located in the dynamic link library hlvdd dll There is a local site in my company that will open fine in other browsers such as firefox safari chrome but when opening the site in IE7 or IE8 displays title HTTP 404 Not Found displayed The webpage cannot be found Store not opening in Windows 8 1 pro Unable to load particular website error Page Cannot be Displayed Unable to boot the computer and getting an error irq level not equal too usb 3 0 port not working or recognised The taskbar is hidden and I cannot get it to appear the hot key does not work W7 backup I am having trouble understanding what is backed up and cannot find correct site to explain Virtual USB port not available in Windows 7 printer port drop down list When ever my computer goes to sleep when your not using it it doesn t turn back on whenever you use the power button Why Webcam not working in Cyberpower PC x6 9600 gaming laptop,2 GIG SD Cards and Windows 7 64 Bit Puzzler After Windows Updates were installed got Blue Screen error now when I try to run programs getting Error code 0xc0000142 Change default program for xlsx files in Windows 7 back to Excel Starter 2010 Audio problem using Movie Maker 2 6 in Windows 7 Burnatonce software will not write to disc in Windows 7 keep getting error messages BSOD after upgraded to Windows 8 1 memory problem Blue Screen of Death on a Lenovo U400 with Windows 7 Cannot change network location for my home gateway Windows 7 by default made it Public now I cannot change any suggestions After a Windows update received message that C drive was not partitioned Computer doesent go in sleep mode Windows 8 1 Cannot get Windows XP to start up Daily crashes after upgrade to Windows 8 Black screen and loud beeps when rebooting or starting Windows 7 Dictionary for Word 2007 Windows 8 App shown in web version of Windows Store but not actually in Windows Store Display port DP and HDMI sync issues and problem with display profiles in Windows 7,Avast Internet Security 5 0 667 is not recoginzed on my Windows 7 computer ati radeon hd 3200 graphics driver update I can t install apps from the Windows Store or updating Windows using Windows Update ati radeon driver download Need 2 Find Windows Explorer Zip per Unzipper ati graphics driver Processor subscore in Windows Experience Index decreased ati radeon hd 3200 graphics driver update Startup asks to select Windows 7 or prior OS ati graphics driver update Windows 7 64 Bit Pro Explorer explorer exe keeps crashing and restarting randomly radeon driver Windows Virtual PC running a Foxpro 2 6a application ATI Radeon HD 4650 Drivers Qualcomm Atheros Wireless Lan Driver and Application DOWNLOAD