Solved IF help required -

June 29, 2011 at 18:36:56
Specs: Windows XP, Pentium dual - E5200 / 3.25 Gb Ram
In an excel spreadsheet, I have two columns with dates, one is a date of incident, the other is a date of receipt of claim.

I want to create a rule that will either conditionally format the cell, or will populate an additional cell with a statement where the timeframe between incident and receipt is more than 60 days (and therefore the claim isn't valid.).

Eg - if date between incident and claim the cell turns red, or additional cell has false statement that can then be formatted, Any help is appreciaated.


See More: IF help required -

Report •

June 29, 2011 at 18:55:51
✔ Best Answer
Try this:

(Note: These steps are for Excel 2003 and earlier. If you are using 2007 or later, the steps are slightly different but the formula (or "rule") would be the same.)

Assumption: The Incident dates are in e.g. A1:A10 and the Claim dates are in e.g. B1:B10,

Select B1:B10
Format...Conditional Formatting
Choose "Formula Is..." from the drop down and use this formula:


Format the cell for Patterns Red

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 29, 2011 at 20:11:11

so simple it eluded me - I was messing with IF formulas and all sorts. Thanks for this. Very much appreciated


Report •

June 30, 2011 at 00:01:44
Many suggestions for Conditional Formatting rules do indeed use IF formulas, but they aren't really necessary.

All that is required is a formula that returns TRUE when the conditions that you are formatting for are met.

Therefore all three of these are equivalent from that perspective:


=IF(B1>A1+60, TRUE, FALSE)

=IF(B1>A1+60, TRUE, "Who cares what goes here?")

If you put any of those in an Excel cell, you'll see that they all return TRUE when B1>A1+60. Whatever else is returned is ignored by the Conditional Formatting feature since it is only looking for a TRUE.

You could also use either of these since 1 is equivalent to TRUE as far as Excel is concerned:

=IF(B1>A1+60, 1, 0)

=IF(B1>A1+60, 1, "Who cares what goes here?")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question