Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting dates due to expire
I have a column in a spreadsheet which lists the various dates a lease will
expire. What I would like to do is to change the format to yellow when the date is due in 90 days, (in other words give me a 3 month warning), and change to red when the date is reached and past. All the other dates will be green to signify lease is still current. Thanking anyone in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting dates due to expire
Assume dates are in col A
Select col A (A1 active), then apply CF using "Formula Is" for Conditions 1 to 3 as follows: Condition 1: =AND(A1<=TODAY(),A1<"") Format Red fill/white font Condition 2: =AND(A1<=TODAY()+90,A1<"") Format Yellow fill Condition 3: =AND(A1TODAY()+90,A1<"") Format Green fill Click to OK out -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Allan Skyner" wrote: I have a column in a spreadsheet which lists the various dates a lease will expire. What I would like to do is to change the format to yellow when the date is due in 90 days, (in other words give me a 3 month warning), and change to red when the date is reached and past. All the other dates will be green to signify lease is still current. Thanking anyone in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting dates due to expire
Select the range of cells to be formatted (I will use A1:A10 as my example)
Use Format | Conditional Formatting from menu Set the dialog to read: Formula Is: =TODAY()-A1=0 then with the Format button on the dialog set the red text or background as needed Use the Add button; for second condition Formula Is =A1-TODAY()<=90 with colour yellow Add the third condition Formula Is A1-Today()90 with colour green Note: my first cell was A1 so that is why A1 appears in the Formulas, adjust to match you range best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Allan Skyner" wrote in message ... I have a column in a spreadsheet which lists the various dates a lease will expire. What I would like to do is to change the format to yellow when the date is due in 90 days, (in other words give me a 3 month warning), and change to red when the date is reached and past. All the other dates will be green to signify lease is still current. Thanking anyone in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting dates | Excel Discussion (Misc queries) | |||
Conditional Formatting - dates | Excel Discussion (Misc queries) | |||
Conditional Formatting-dates | Excel Worksheet Functions | |||
conditional formatting using dates | Excel Discussion (Misc queries) | |||
conditional formatting with dates | Excel Worksheet Functions |