I a have a great spreadsheet i use to keep up with my teammates training dates. They have things that are annual, and bi-annual. How do I get the cells with dates in them to turn Red if they are due or past due? Example: training was done on 4/2/2008, and needs to be done again 4/2/2009? I have already used 2 conditions, one for turning cells with N's in them red and another for turning Cells with N/A's in them Gray. Would love to have cells that are 1 month to the due date change colors. So that I have time to get their training done. Please help i've been working on this for 3 days, and am completely lost and thinking this might be totally hopeless. Thanks Karen

Try this conditional formatting formula: Assume A1 holds the due date: =IF(AND(A1-TODAY()>0,A1-TODAY()<30),TRUE,FALSE)

ok, I've seen that Formula. Its more than 1 cell that i need this formula for. Is there any way to change it to fit any cells i highlight?

Highlight all the cells you want this applied to and then do your Condidtional Formatting for the first cell in the range. Excel will apply the Conditional Formatting to all cells

Im sorry you lost me on that last post. I'm not very good with Excel. what do you mean put in the ranges? I attempted that formula in just one cell, and it didn't work. Is there any way to put it in as Cell Value is Less than ..........? to me it seems that's the way i should be leaning, but no idea what to type in to get the dates that are past due (more than a year less today() date, or will be due in 30 days prior to the same. I should also tell you that the dates are coming from another tab on the spreadsheet, by copy and paste Link. I'm sorry i didn't give you that info before. Is that why its not working for me?

You need to do "Conditional Formatting" on all of the cells that contain your dates. So your first training date - "Example: training was done on 4/2/2008" should go in Column A Cell 1 All subsequent training dates should follow in A2, A3, A4 etc. Place your cursor in cell A1 Highlight all of the Training Date Cells, for example A1 thru A50 On the Tool Bar click Format Then click "Conditional Formatting" Change the first window from "Cell Value Is" to "Formula Is" Enter DerbyDad03's formula: =IF(AND(A1-TODAY()>0,A1-TODAY()<30),TRUE,FALSE) Click the "Format" button Select the Patterns Tab Select your favorite color Click OK Click OK MIKE http://www.skeptic.com/

I put 5/1/2009 in Sheet2!A1 I put =Sheet2!A1 in Sheet1!A1 so it also shows 5/1/2009 I used Conditional Formatting on Sheet1!A1 as follows: Formula is =IF(AND(A1-TODAY()>0,A1-TODAY()<30),TRUE,FALSE) Format...Pattern...Red Sheet1!A1 turned Red because today's date (4/2/2009) is less than 30 days from 5/1/2009. It doesn't matter that the original date is on another sheet. The Conditional Formatting is based on the result of the formula in the cell - in this case, 5/1/2009. I then changed Sheet2!A1 to be 6/1/2009, which caused Sheet1!A1 to display 6/1/2009 and the Red went away because today's date (4/2/2009) is not less than 30 days from 6/1/2009. If I wanted to apply the same conditional formatting to a range (or group) of cells, I would select something like A1:A10 then enter same formula in the Conditional Formatting...Formula is...field and it would set the same Conditional Formatting for each cell, but update the formula (A1, A2, A3, etc.) to match each cell.