Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
One Cell contains a Due Date, and a second cell contains a Completed Date.
1. I would like to have the background of the Due Date cell to change to yellow only when the date in this cell is within one week of todays date and the Completed Date cell is still null, otherwise the background remains white or transparent. 2. I would like to have the background of the Due Date Cell to change to red only when the date in this cell is older than todays date and the Completed Date cell is null. Any help greatly appreciated - Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
One way:
Assume the Due date in cell in A1, the Completed date in B1 CF1: Formula is =AND(LEN(B1)=0,A1<TODAY()) Format1: <patterns/<red CF2: Formula is =AND(LEN(B1)=0,A1<(TODAY()+7)) Format2: <patterns/<yellow In article , Pete Sperling wrote: One Cell contains a Due Date, and a second cell contains a Completed Date. 1. I would like to have the background of the Due Date cell to change to yellow only when the date in this cell is within one week of todays date and the Completed Date cell is still null, otherwise the background remains white or transparent. 2. I would like to have the background of the Due Date Cell to change to red only when the date in this cell is older than todays date and the Completed Date cell is null. Any help greatly appreciated - Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
You can do this in conditional formatting found on the format menu.
First thing though I would do in a cell thats not being used is type =today() This will then shows todays date, so say this is cell "A1" Say Cell "B2" has the due date, and Cell "C2" has the completed date. ok say you need cell b2 to change colour, select this cell, go format, conditional format. This is condition one, on the drop down menu select "Formula Is" Then type in the following =IF(AND(B2<$A$1,C2=""),TRUE) Then click format button, click pattens and change the sample colour to red, then ok. Click the add button, your now see condition 2, again This is condition two, on the drop down menu select "Formula Is" Then type in the following =IF(AND(OR(B2=$A$1,B2<=$A$1+7),C2=""),TRUE) Then click format button, click pattens and change the sample colour to yellow, then ok. Then ok button. Should now work as required. -- This post was created using recycled electrons! "Pete Sperling" wrote: One Cell contains a Due Date, and a second cell contains a Completed Date. 1. I would like to have the background of the Due Date cell to change to yellow only when the date in this cell is within one week of todays date and the Completed Date cell is still null, otherwise the background remains white or transparent. 2. I would like to have the background of the Due Date Cell to change to red only when the date in this cell is older than todays date and the Completed Date cell is null. Any help greatly appreciated - Pete |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Newbeetle - Your below works great - one more thing - how do I add a third
condition which leaves the Due Date cell with no color if there is no date in the cell. I'm trying to prevent having to add the conditions each time a new item is created in the list. Thanks for your assistance. - Pete "Newbeetle" wrote: You can do this in conditional formatting found on the format menu. First thing though I would do in a cell thats not being used is type =today() This will then shows todays date, so say this is cell "A1" Say Cell "B2" has the due date, and Cell "C2" has the completed date. ok say you need cell b2 to change colour, select this cell, go format, conditional format. This is condition one, on the drop down menu select "Formula Is" Then type in the following =IF(AND(B2<$A$1,C2=""),TRUE) Then click format button, click pattens and change the sample colour to red, then ok. Click the add button, your now see condition 2, again This is condition two, on the drop down menu select "Formula Is" Then type in the following =IF(AND(OR(B2=$A$1,B2<=$A$1+7),C2=""),TRUE) Then click format button, click pattens and change the sample colour to yellow, then ok. Then ok button. Should now work as required. -- This post was created using recycled electrons! "Pete Sperling" wrote: One Cell contains a Due Date, and a second cell contains a Completed Date. 1. I would like to have the background of the Due Date cell to change to yellow only when the date in this cell is within one week of todays date and the Completed Date cell is still null, otherwise the background remains white or transparent. 2. I would like to have the background of the Due Date Cell to change to red only when the date in this cell is older than todays date and the Completed Date cell is null. Any help greatly appreciated - Pete |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
JE McGimpsey - Your suggestion works great - How would I add a third
condition which leaves cell A1 with no color if the Due date cell is null. I'm trying to prevent having to enter the conditions in the Due date cells each time I add a new action item to the list. Thanks for your assistance - Pete "JE McGimpsey" wrote: One way: Assume the Due date in cell in A1, the Completed date in B1 CF1: Formula is =AND(LEN(B1)=0,A1<TODAY()) Format1: <patterns/<red CF2: Formula is =AND(LEN(B1)=0,A1<(TODAY()+7)) Format2: <patterns/<yellow In article , Pete Sperling wrote: One Cell contains a Due Date, and a second cell contains a Completed Date. 1. I would like to have the background of the Due Date cell to change to yellow only when the date in this cell is within one week of todays date and the Completed Date cell is still null, otherwise the background remains white or transparent. 2. I would like to have the background of the Due Date Cell to change to red only when the date in this cell is older than todays date and the Completed Date cell is null. Any help greatly appreciated - Pete |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Hi,
you don't need to add another condition, instead change the formula as follows; Condition 1 =IF(AND(B2<$A$1,C2="",B2<""),TRUE) Condition 2 =IF(AND(OR(B2=$A$1,B2<=$A$1+7),C2="",B2<""),TRUE) if its a blank cell it should stay white. -- This post was created using recycled electrons! "Pete Sperling" wrote: Newbeetle - Your below works great - one more thing - how do I add a third condition which leaves the Due Date cell with no color if there is no date in the cell. I'm trying to prevent having to add the conditions each time a new item is created in the list. Thanks for your assistance. - Pete "Newbeetle" wrote: You can do this in conditional formatting found on the format menu. First thing though I would do in a cell thats not being used is type =today() This will then shows todays date, so say this is cell "A1" Say Cell "B2" has the due date, and Cell "C2" has the completed date. ok say you need cell b2 to change colour, select this cell, go format, conditional format. This is condition one, on the drop down menu select "Formula Is" Then type in the following =IF(AND(B2<$A$1,C2=""),TRUE) Then click format button, click pattens and change the sample colour to red, then ok. Click the add button, your now see condition 2, again This is condition two, on the drop down menu select "Formula Is" Then type in the following =IF(AND(OR(B2=$A$1,B2<=$A$1+7),C2=""),TRUE) Then click format button, click pattens and change the sample colour to yellow, then ok. Then ok button. Should now work as required. -- This post was created using recycled electrons! "Pete Sperling" wrote: One Cell contains a Due Date, and a second cell contains a Completed Date. 1. I would like to have the background of the Due Date cell to change to yellow only when the date in this cell is within one week of todays date and the Completed Date cell is still null, otherwise the background remains white or transparent. 2. I would like to have the background of the Due Date Cell to change to red only when the date in this cell is older than todays date and the Completed Date cell is null. Any help greatly appreciated - Pete |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Pete, a major benefit with the way JE McGimpsey has laid out the formula, is
you don't need to have a cell with Today() in it, thats just something I do, as I tend to refer other cells to one throughout my worksheets, and give me one reference to change for testing etc. If you want to remove it, just modify the formulas too; Condition 1 =IF(AND(B2<Today(),C2="",B2<""),TRUE) Condition 2 =IF(AND(OR(B2=Today(),B2<=Today()+7),C2="",B2<"") ,TRUE) Should work as required. -- This post was created using recycled electrons! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
CF1: Formula is =AND(LEN(B1)=0,A1<"",A1<TODAY())
Format1: <patterns/<red CF2: Formula is =AND(LEN(B1)=0,A1<"",A1<(TODAY()+7)) Format2: <patterns/<yellow In article , Pete Sperling wrote: JE McGimpsey - Your suggestion works great - How would I add a third condition which leaves cell A1 with no color if the Due date cell is null. I'm trying to prevent having to enter the conditions in the Due date cells each time I add a new action item to the list. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |