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.

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.

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.

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.

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.