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.
Thank You in advance.
You can do what you want with conditional formatting.
There is a formula in excel that yields the current day, that formula is
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.
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
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
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