Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting Formula using AND
I have two columns in a worksheet, one with date (Column J) and another with
status (Column K). I would like assistance with a formula that will enable me to colour the cell in column J red if the date is more than 7 days ago and Column K does not have the word "Complete" or "Quoted" I have tried to use the following formaula which is not working correctly, can somebody please assist? =AND(K2<OR(K2="Complete",K2="Quoted"),J2<=TODAY()-7) Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting Formula using AND
Try
=AND(OR(K2="Complete",K2="Quoted"),J2<(TODAY()-6)) "Anton" wrote: I have two columns in a worksheet, one with date (Column J) and another with status (Column K). I would like assistance with a formula that will enable me to colour the cell in column J red if the date is more than 7 days ago and Column K does not have the word "Complete" or "Quoted" I have tried to use the following formaula which is not working correctly, can somebody please assist? =AND(K2<OR(K2="Complete",K2="Quoted"),J2<=TODAY()-7) Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting Formula using AND
Thanks this works great for the date part but almost the oposite to what I
want for the other, I would like to colour the date in Column J red when Column K DOES NOT have the word "Complete" or "Quoted" Thanks in advance "Sheeloo" wrote: Try =AND(OR(K2="Complete",K2="Quoted"),J2<(TODAY()-6)) "Anton" wrote: I have two columns in a worksheet, one with date (Column J) and another with status (Column K). I would like assistance with a formula that will enable me to colour the cell in column J red if the date is more than 7 days ago and Column K does not have the word "Complete" or "Quoted" I have tried to use the following formaula which is not working correctly, can somebody please assist? =AND(K2<OR(K2="Complete",K2="Quoted"),J2<=TODAY()-7) Thank you in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting Formula using AND
Sorry, I misunderstood.
Put a NOT around the OR like this; =AND(NOT(OR(K2="Complete",K2="Quoted")),J2<(TODAY( )-6)) "Anton" wrote: Thanks this works great for the date part but almost the oposite to what I want for the other, I would like to colour the date in Column J red when Column K DOES NOT have the word "Complete" or "Quoted" Thanks in advance "Sheeloo" wrote: Try =AND(OR(K2="Complete",K2="Quoted"),J2<(TODAY()-6)) "Anton" wrote: I have two columns in a worksheet, one with date (Column J) and another with status (Column K). I would like assistance with a formula that will enable me to colour the cell in column J red if the date is more than 7 days ago and Column K does not have the word "Complete" or "Quoted" I have tried to use the following formaula which is not working correctly, can somebody please assist? =AND(K2<OR(K2="Complete",K2="Quoted"),J2<=TODAY()-7) Thank you in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting Formula using AND
Thanks Heaps! This works great!
"Sheeloo" wrote: Sorry, I misunderstood. Put a NOT around the OR like this; =AND(NOT(OR(K2="Complete",K2="Quoted")),J2<(TODAY( )-6)) "Anton" wrote: Thanks this works great for the date part but almost the oposite to what I want for the other, I would like to colour the date in Column J red when Column K DOES NOT have the word "Complete" or "Quoted" Thanks in advance "Sheeloo" wrote: Try =AND(OR(K2="Complete",K2="Quoted"),J2<(TODAY()-6)) "Anton" wrote: I have two columns in a worksheet, one with date (Column J) and another with status (Column K). I would like assistance with a formula that will enable me to colour the cell in column J red if the date is more than 7 days ago and Column K does not have the word "Complete" or "Quoted" I have tried to use the following formaula which is not working correctly, can somebody please assist? =AND(K2<OR(K2="Complete",K2="Quoted"),J2<=TODAY()-7) Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting-If Formula | Excel Worksheet Functions | |||
Conditional formatting as a formula | Excel Worksheet Functions | |||
Conditional Formatting: Formula is | Excel Worksheet Functions | |||
conditional formatting formula | Excel Discussion (Misc queries) | |||
Conditional Formatting Formula | New Users to Excel |