# How to format a date field based on whether the date has passed?

I am trying to create an Excel spreadsheet where, when I input a date:

1. if that date is within 30 days it will go yellow,
2. if the date has already gone then it goes red, and
3. if the date is more than 30 days away it remains green.

How can I do that?

-
What version of excel? – Brad Patton Mar 16 '13 at 20:20

I don't believe you can do this directly in a formula but you can use the Conditional Formatting dropdown with a formula.

This blog post has a good writeup of the steps needed. Also this article walks thru the steps.

-

In Excel 2010 (possibly earlier versions) you can use a formula to determine formatting.

Select the cell with your date in it and then select conditional formatting, new rule. Then "Use a formula to determine which cells to format" Use something like `=IF(Today()>I7,1,0)` - I7 is the date being formatted - and then select the type of formatting - you want a fill with the appropriate colour. That particular formula checks if your selected date is older than todays date. You can have multiple formatting rules per cell.

Change the formula and colours as necessary. This should do for more than 30 days in the future: `=IF(I7>TODAY()+30,1,0)`

This should do for the next 30 days rule: `=IF(TODAY()<I7+30,1,0)`

Rules will need to be in the right order. This may not be the most elegant way but it's a start.

-
The IF functions will work but in conditional formatting you only really need the "test" part, e.g. for the first condition `=TODAY()>I7` and similar for the others – barry houdini Mar 16 '13 at 23:22

I could do it in Excel 2010 by adding two rules to the cells where the dates are inputted.

Choose Conditional Formatting > Manage Rules

1st rule > "Use a formula to determine which cells to format"

``````=IF(INDIRECT("R[0]C[0]",0)>TODAY()+30,1,0)
``````

Choose Format > Green

2nd rule >

``````=IF(INDIRECT("R[0]C[0]",0)<TODAY(),1,0)
``````

Choose Format > Red

Check "Stop if true" for each of the rules in the Conditional Formatting Rules Manager, and then apply a yellow base formatting to the cell which will be default if the conditions do not apply.

-