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.

Thanks

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:=B1>A1+60

Format the cell for Patterns

Red

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

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

Joe

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:

=B1>A1+60

=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.

Ask Your Question

Weekly Poll

Do you think Europe's new data protection laws will affect you?

Discuss in The Lounge

Poll History