Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula in conditional formatting with dates
Cell A1 = doc status with the following possible values:
"Due", "Revise", "Sign", "Complete". Cell A2 contains a date. I need a formula for conditional formatting that will turn Cell A2 yellow with blue bold font if A1="Due" or "Sign" AND Cell A2 is within 31 days of today's date. Todays date is located in cell A3 as =TODAY(). I have used the conditional formatting function alot in very basic ways, but am somehow just not able to work out this formula. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula in conditional formatting with dates
Select A2 and click on Format | Conditional Formatting. In the
dialogue box select Formula Is rather than Cell Value Is, and enter this formula: =AND(OR(A1="Due",A1="Sign"),A2TODAY()-31) Click on the Format button to set your colours and Bold (Patterns tab for background colour), then OK twice to exit the dialogue. You don't need to use A3 (unless you want it for something else, in which case put A3 instead of TODAY() in the formula). Hope this helps. Pete On Nov 16, 1:13 am, MelB wrote: Cell A1 = doc status with the following possible values: "Due", "Revise", "Sign", "Complete". Cell A2 contains a date. I need a formula for conditional formatting that will turn Cell A2 yellow with blue bold font if A1="Due" or "Sign" AND Cell A2 is within 31 days of today's date. Todays date is located in cell A3 as =TODAY(). I have used the conditional formatting function alot in very basic ways, but am somehow just not able to work out this formula. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula in conditional formatting with dates
On Nov 16, 10:33 am, Pete_UK wrote:
Select A2 and click on Format | Conditional Formatting. In the dialogue box select Formula Is rather than Cell Value Is, and enter this formula: =AND(OR(A1="Due",A1="Sign"),A2TODAY()-31) Click on the Format button to set your colours and Bold (Patterns tab for background colour), then OK twice to exit the dialogue. You don't need to use A3 (unless you want it for something else, in which case put A3 instead of TODAY() in the formula). Hope this helps. Pete On Nov 16, 1:13 am, MelB wrote: Cell A1 = doc status with the following possible values: "Due", "Revise", "Sign", "Complete". Cell A2 contains a date. I need a formula for conditional formatting that will turn Cell A2 yellow with blue bold font if A1="Due" or "Sign" AND Cell A2 is within 31 days of today's date. Todays date is located in cell A3 as =TODAY(). I have used the conditional formatting function alot in very basic ways, but am somehow just not able to work out this formula.- Hide quoted text - - Show quoted text - if you want to ignore dates that are in the future you can change the formula to this: =AND(OR(A1="Due",A1="Sign"),AND(A2TODAY()-31,A2<=TODAY())) hth Carlo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula in conditional formatting with dates
Hi Mel
Try =AND(A2-A3<=31,A2-A30,OR(A1="Due",A1="Sign")) -- Regards Roger Govier "MelB" wrote in message ... Cell A1 = doc status with the following possible values: "Due", "Revise", "Sign", "Complete". Cell A2 contains a date. I need a formula for conditional formatting that will turn Cell A2 yellow with blue bold font if A1="Due" or "Sign" AND Cell A2 is within 31 days of today's date. Todays date is located in cell A3 as =TODAY(). I have used the conditional formatting function alot in very basic ways, but am somehow just not able to work out this formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting formula for past due dates | Excel Discussion (Misc queries) | |||
Using Conditional Formatting for Dates | Excel Discussion (Misc queries) | |||
conditional formatting with dates | Excel Worksheet Functions | |||
Conditional formatting with dates formula problem. | Excel Discussion (Misc queries) | |||
Conditional Formatting Dates | Excel Worksheet Functions |