# Help with Dates in Excel 2003

April 2, 2009 at 12:06:37
Specs: Windows 2000
 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

See More: Help with Dates in Excel 2003

#1
April 2, 2009 at 12:39:22
 Try this conditional formatting formula:Assume A1 holds the due date:=IF(AND(A1-TODAY()>0,A1-TODAY()<30),TRUE,FALSE)

Report •

#2
April 2, 2009 at 12:47:04
 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?

Report •

#3
April 2, 2009 at 13:34:55
 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

Report •

Related Solutions

#4
April 2, 2009 at 15:14:35
 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?

Report •

#5
April 2, 2009 at 17:43:30
 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 1All subsequent training dates should follow in A2, A3, A4 etc.Place your cursor in cell A1Highlight all of the Training Date Cells, for example A1 thru A50On the Tool Bar click FormatThen 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" buttonSelect the Patterns TabSelect your favorite colorClick OKClick OKMIKE

Report •

#6
April 2, 2009 at 17:54:27
 I put 5/1/2009 in Sheet2!A1I put =Sheet2!A1 in Sheet1!A1 so it also shows 5/1/2009I used Conditional Formatting on Sheet1!A1 as follows:Formula is =IF(AND(A1-TODAY()>0,A1-TODAY()<30),TRUE,FALSE)Format...Pattern...RedSheet1!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.

Report •

#7
April 2, 2009 at 17:58:58
 A clarification on Mike's instructions:He said: So your first training date - "Example: training was done on 4/2/2008" should go in Column A Cell 1Actually, only the date itself can be in the cell for the Conditional Formatting to work. If you put everything within the quotes in the cell, it will never change color.

Report •

#8
April 2, 2009 at 18:58:07
 I have tried using the 1 cell with a date filled in and placing that cell # into the formula you all are giving me. But its not turning my dates colors. Maybe im thinking this process wrong. The date that the cell has is 2/21/07 and some are older than that. I need those dates to become highlighted because they aren't a year ago from today() . Some are a year ago from Today() and those are fine. I can delete a conditional formatting if the cells that are 30 days prior to the year ago from Today(). I'm sorry I seem to be making this overly difficult, and i really do not mean to. Thank you for all your help so far.

Report •

#9
April 2, 2009 at 19:54:58
 OK, new Conditional Formatting formula:=IF(DATEDIF(A1,TODAY(),"M")>=13,TRUE,FALSE)This will highlight all dates older than or equal to 13 months.MIKE

Report •