Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Trying to do an insert if formula in excel like the following:

In my column K I want three conditions:

  • Date is Today or older (ie project is due today or earlier) = RED
  • Date is Today + up to 7 days = Amber
  • Date is Today less more than 7 days = Green

I don't want to use conditional formatting as the rules are too rigid :)

something along the lines of:

=IF(TODAY(-7d),ChangeFillColor))

I've been trying but I'm not sure how to do it, also open to VBA if necessary :)

share|improve this question
1  
Can U explain “rules are too rigid” pse? Conditional formatting is specifically designed for what seems to be your requirement –other than you want to manage without! (eg for Excel 2007: [1] select Column K; [2] select Conditional Formatting, Manage Rules…, New Rule…, Format all cells based on their values, Format Style = Icon Sets, Icon Style =3 Symbols (Circled); [3] select Type=Formula (twice), put =NOW()+7 in Value (upper) and =NOW() in Value (lower), click <kbd>OK</kbd>, <kbd>Apply</kbd>, <kbd>OK</kbd>. Default settings should otherwise be suitable, but might need to be ‘tweaked’.) –  pnuts Jul 18 '12 at 15:34

2 Answers 2

up vote -1 down vote accepted

An IF() statement will work for before today's date:

=IF(TODAY()>=10/10/2010, "Do something")
share|improve this answer

I don't think a formula can directly change the fill color of a cell. However, you can use a formula that outputs the actual name of the color you want (this formula assumes that your date is in cell A1):

=IF(A1<TODAY()+1,"red",IF(A1<TODAY()+8,"amber","green"))

Then, you will have to use conditional formatting to actually color the cell based on the name of the color. For example, set conditional formatting so that the word red changes the fill color to Red. This way, you won't have a complicated formula in the conditional formatting. The calculation will take place in the formula actually in that cell. If you don't want the names of the colors to be visible, you can set the conditional formatting so that it also changes the font color to the same as the fill color ("Invisible Version" in the example).

If you are trying to color the cells that actually contain the dates, the formula logic will need to be contained inside of the conditional formatting. This is shown in the "One Column Version" in the example.

Here is an example spreadsheet: https://dl.dropbox.com/u/3488338/DueDateFormula.xlsx

Screenshot of example spreadsheet

share|improve this answer
    
@pnuts I'm sorry, but I don't know what you mean by CF, PS and OP. I am willing to work with you to adapt this solution to fit your needs. When I read the original question, I for some reason thought that the asker had dates in one column and wanted to color code a different column. However, this could also be done without the extra column. I have updated the example spreadsheet to include a "one column" implementation. In this version, the formula that checks the date ranges is located inside of the conditional formatting. –  bdr9 Apr 12 '13 at 0:41
1  
@pnuts I have rolled it back. I still think that it is a good idea to include a screenshot in my answer, so I re-added the screenshot that I had made of the new example spreadsheet. I used the icon to the right of the braces to insert the screenshot, so I don't think the link will fail. Thanks! –  bdr9 Apr 12 '13 at 0:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.