Format Cell Color based on value (2000 sp3)

Topic

New Reply

Greetings all,

I have a spreadsheet where I have the user entering date information into different cells. Received and Complete. since this is manual entry, they sometimes enter the complete date as a date before the received date.

I need to turn the Received date a color if the Complete date is less than the Received date. This also needs to understand if the complete date is blank, to not change the color.

Example:

Received date Complete Date
3-Jun-2005 4-Jun-2005
4-Jul-2005 1-Mar-2005
1-Jul-2005

4-Jul-2005 is greater than 1-Mar-2005, therefore 4-Jul-2005 cell should change color. 1-Jul-2005 is in received, but the complete date is blank. So, cell for 1-Jul-2005 should remain netural.

Thanks,
Brad

Reply | Quote

Replies

Say that the received dates are in column A and the complete dates in column B, starting in row 2 (row 1 contains the column headers)
Select A2:A100 or as far down as needed.
Select Format | Conditional Formatting…
Select Formula Is from the first dropdown list.
Enter the following formula in the box next to it:

=AND(B2"",A2>B2)

Click Format… and specify the desired formatting.
Click OK twice.

Reply | Quote

Hans,
works great! (as always)

Brad

Reply | Quote