You are here:

# Excel/conditional formating

Question
I am working on a spreadsheet for work that hopefully when I am finish will cut down on a lot of time on searching for due dates at the beginning of the month for 50 employees soon to be 150.  I would like to develop a form or a spreadsheet that will tell me when due dates are coming up or expired.  If their training is up to date I would like that cell(with the expiration date) to be green, when their training is 30 days before due I will like that cell to become yellow and when their training is expired I will like that cell to become red.
I will write the employee's name in column A, and the expiration dates of the training in column B.   Please let me know if you can help me, or show me an easier way to do this.  I am using excel 2010.

Yris

You can do what you want with conditional formatting.
There is a formula in excel that yields the current day, that formula is
=NOW()

So as i see it you have three conditions and they are

1--your expiration date cell value is less than Now() or the training has expired.

2--Your expiration date cell value is greater than NOW() but less than Now() plus 30 days

3--You expiration date cell value is greater than NOW() plus 30 days.

I would create this formula in column adjacent to your expiration date column or column C.

=IF(B2<NOW(),1,0)+IF(AND(B2<NOW()+30,B2>NOW()),2,0)+IF(B2>NOW()+30,3)

As you can see the formula simply states the above criteria and assigns a number to each one
1 for expired
2 for within 30 days
3 for up to date

Now you need to use conditional formatting to format these cells as
1= red
2=yellow
3=green

To do the conditional formatting first select the top cell that has the above formula in it I believe that this will be your cell C2.  Then go to toolbar/home/styles/conditional formatting  (some toolbars are set up differently so just look for the conditional formatting icon)

Clic on the conditional formatting icon then clic on highlight cell rules, then clic on equal to.  In the box that says format cells that are equal to
put in =c2=1.  Then in the with box click on the drop down then select custom then select fill then select the red color that you want, then clic ok.  in the next box put in =c2=2 then select the yellow formatting color.  Then repeat with =c2=3 and select green.

You can now copy down the formula and the formatting to the cells below C2.

Hope this helps

Richard
Florida
USA
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Richard Roberts

##### Expertise

Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.

##### Experience

Have been working with Excel for about 20 years primarily in accounting and financial areas.

Education/Credentials
BA, Certified Public Accountant