I have an issue. If this is not possible or there is another way to do this I would love to know. Any help is appreciated.
I have a worksheet with a column for dates and another column for due dates. ex. 10/20/2012 in last done column. Maintenance required in another column. I have a formula comparing the fixed due dates against today's date and the last done date to warn that maintenance is required by returning a logical 1 or 0 in a separate column and conditionally formatting it to turn red. This is the formula I am using. =IF((TODAY()-L19)>=120,1,0)
Where 120 is a set amount of days before a required maintenance and L is the last performed maintenance. What I would like is for the column with conditional formatting to have multiple colors to warn of the upcoming maintenance at different intervals based on color before the actual due date all in a single column. Like if maintenance is one month or greater away as green, 2 weeks as orange and due and or overdue as red. I currently have it as logical returns 1 and cells are red, 0 and cells are white. Now the only way I could figure this would be remotely possible is to have nested IF statements, but IF is still based on the first logical true. What I was considering was can you have a formula that says if cell L# displays a date more than 120 days out than it returns say a 1 for true and a 0 for false and on top of that says at 113 days out 1 week before the due date return 2 if true and 0 if false and on and on for the due dates at intervals before they are due at say 1 week, 2 weeks, a month. Then the column with the true returns could be formatted by color based on the number of the true return. Right now it can only show that the maintenance is due since it only returns a 1 or a 0 that turns the column red.
Last Done - 10/20/2012
Frequency 120 days
Maintenance required 1=red cell
0=white cell
Bookmarks