Conditional formatting based on comparing 2 dates (fields)

Question

This seems like it should be simple, but I'm scratching my head.  I want to compare an initial date with a revised date and have the revised date cell format differently (such as red) if the date is later.  I'm using Excel 2007 if that matters.  This would be applied to columns of values.  What is a good way to handle this?

Thanks!

Answer 1

Let's say initial dates are in B2:B50 and revised dates in D2:D50.

Select D2:D50. I will assume that D2 is the active cell within the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first dropdown set to 'Cell Value'.

Select 'greater than' from the second dropdown.

Enter   =B2   in the box next to it.

Click Format...

Specify the desired formatting.

Click OK twice.

Answer 2

Select the range again.

Click Conditional Formatting > Manage Rules...

Make sure the rule is selected, then click Edit Rule...

Click 'Use a formula to determine which cells to format'.

Enter the formula   =AND($B2<>"",$D2>$B2)

(Note the use of $)

OK your way out.

Answer 3

Hi Hans,

Thanks so much - this is the formula I couldn't put together!  I appreciate your help!

Scott

Answer 4

Thanks Hans for your help.  I only needed to change the cell reference from $B$2 to $B2 so that each row related to the data in that record. 

One further questions.  If there is a date entered into colum D but column B is blank for that row, the date is auto formatted.  Is there a simple way to not format if column B is blank for a row?

Many thanks again!

Scott