Conditional Formatting in relation to date

Mikey - Jul 27, 2010 at 03:41 AM
 Trowa - Jul 29, 2010 at 09:47 AM

I'm setting up a Warrenty management system and want to colour code parts that are still covered by the warrenty and parts that the warrenty has expired for.

Therefore, I'm trying to find a formula and instructions for shading date values in a column on a worksheet based on the warrenty expiry date (listed down one column) relative to today's date.

For example a cell that shows the warrenty expiring date 27/07/2010 when the current date is 01/01/2008 would be shaded green, and a cell that shows the date 01/01/01 when the current date is 05/07/02 would be shaded Red.

Thanks in Advance for your help.

<config>Windows XP / Excel 2007

1 response

Hi Mikey,

Start by selecting the cells of the column you want to color.
Activate conditional format.
Condition 1: Formula is: =A$1>=TODAY()
Choose color green.
Condition 2: Formula is: =A$1<VANDAAG()
Choose color red.

Now drag the colored cells across the rest of your data, which will look all screwed up for the moment, and look for a small square with dragging/carry through options. Select format only and your done.

Best regards,