0

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 :)

Improve this question
0

2 Answers 2

Reset to default
-1

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

=IF(TODAY()>=10/10/2010, "Do something")
Improve this answer
2

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

Improve this answer
2
  • @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, 2013 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, 2013 at 0:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .