Conditional Format with dates
 

I have an expected due date in column A and an actual date in column B (if
there is no data a #N/A appears). I have a conditional format set to remind
me of upcoming, but am having trouble with one to tell me about past due.

A B
6/26/08 6/25/08
9/24/08 #N/A
3/20/08 3/25/08
11/18/08 #N/A

My first condition is: =AND(A3<TODAY()+30,A3=TODAY())

This one works fine, it highlights the cells of upcoming appointments.

My Second condition is: =AND(A3<TODAY(),A3="#N/A")

The second condition is not doing anything. I am missing something easy,
thanks for your help.

Conditional Format with dates
 

Use ISNA(A3) instead of A3="#N/A"

since #N/A represents 'value is not available' it can not be compared to
anything...

"Lost in Microbiology" wrote:

I have an expected due date in column A and an actual date in column B (if
there is no data a #N/A appears). I have a conditional format set to remind
me of upcoming, but am having trouble with one to tell me about past due.

A B
6/26/08 6/25/08
9/24/08 #N/A
3/20/08 3/25/08
11/18/08 #N/A

My first condition is: =AND(A3<TODAY()+30,A3=TODAY())

This one works fine, it highlights the cells of upcoming appointments.

My Second condition is: =AND(A3<TODAY(),A3="#N/A")

The second condition is not doing anything. I am missing something easy,
thanks for your help.

Conditional Format with dates
 

try this

=AND(A3<TODAY(),(ERROR.TYPE(B3)=7))

On Oct 21, 8:51*pm, Lost in Microbiology
wrote:
I have an expected due date in column A and an actual date in column B (if
there is no data a #N/A appears). I have a conditional format set to remind
me of upcoming, but am having trouble with one to tell me about past due.

A * * * * * * * *B
6/26/08 * * * 6/25/08
9/24/08 * * * #N/A
3/20/08 * * * 3/25/08
11/18/08 * * #N/A

My first condition is: =AND(A3<TODAY()+30,A3=TODAY())

This one works fine, it highlights the cells of upcoming appointments.

My Second condition is: =AND(A3<TODAY(),A3="#N/A")

The second condition is not doing anything. I am missing something easy,
thanks for your help.

Conditional Format with dates
 

Thanks, it worked for those with N/A. Please note, the #N/A was pasted as a
value from a different program into the cell. I received this file from a
client and am trying to flag action items.

However, the new problem, is that even when a date value I entered in column
B is present, it still highlights the date as past due. Is there some other
cell property I sould check?

"Sheeloo" wrote:

Use ISNA(A3) instead of A3="#N/A"

since #N/A represents 'value is not available' it can not be compared to
anything...

"Lost in Microbiology" wrote:

I have an expected due date in column A and an actual date in column B (if
there is no data a #N/A appears). I have a conditional format set to remind
me of upcoming, but am having trouble with one to tell me about past due.

A B
6/26/08 6/25/08
9/24/08 #N/A
3/20/08 3/25/08
11/18/08 #N/A

My first condition is: =AND(A3<TODAY()+30,A3=TODAY())

This one works fine, it highlights the cells of upcoming appointments.

My Second condition is: =AND(A3<TODAY(),A3="#N/A")

The second condition is not doing anything. I am missing something easy,
thanks for your help.

Conditional Format with dates
 

Thanks for the help and prodding.

I just used Ctrl-F to find and Replace the section I was working on with
blanks intead of the #N/A. Then changed the second condition to ISBLANK(). It
works fine and takes a lot of "noise" out of the spreadsheet.

Thanks again for the help.

"Lost in Microbiology" wrote:

Thanks, it worked for those with N/A. Please note, the #N/A was pasted as a
value from a different program into the cell. I received this file from a
client and am trying to flag action items.

However, the new problem, is that even when a date value I entered in column
B is present, it still highlights the date as past due. Is there some other
cell property I sould check?

"Sheeloo" wrote:

Use ISNA(A3) instead of A3="#N/A"

since #N/A represents 'value is not available' it can not be compared to
anything...

"Lost in Microbiology" wrote:

I have an expected due date in column A and an actual date in column B (if
there is no data a #N/A appears). I have a conditional format set to remind
me of upcoming, but am having trouble with one to tell me about past due.

A B
6/26/08 6/25/08
9/24/08 #N/A
3/20/08 3/25/08
11/18/08 #N/A

My first condition is: =AND(A3<TODAY()+30,A3=TODAY())

This one works fine, it highlights the cells of upcoming appointments.

My Second condition is: =AND(A3<TODAY(),A3="#N/A")

The second condition is not doing anything. I am missing something easy,
thanks for your help.

Conditional Format with dates
 

=AND(A3<TODAY(),A3="#N/A") will always return FALSE unless A3="#N/A"
use
=OR(A3<TODAY(),A3="#N/A")

"Lost in Microbiology" wrote:

Thanks, it worked for those with N/A. Please note, the #N/A was pasted as a
value from a different program into the cell. I received this file from a
client and am trying to flag action items.

However, the new problem, is that even when a date value I entered in column
B is present, it still highlights the date as past due. Is there some other
cell property I sould check?

"Sheeloo" wrote:

Use ISNA(A3) instead of A3="#N/A"

since #N/A represents 'value is not available' it can not be compared to
anything...

"Lost in Microbiology" wrote:

I have an expected due date in column A and an actual date in column B (if
there is no data a #N/A appears). I have a conditional format set to remind
me of upcoming, but am having trouble with one to tell me about past due.

A B
6/26/08 6/25/08
9/24/08 #N/A
3/20/08 3/25/08
11/18/08 #N/A

My first condition is: =AND(A3<TODAY()+30,A3=TODAY())

This one works fine, it highlights the cells of upcoming appointments.

My Second condition is: =AND(A3<TODAY(),A3="#N/A")

The second condition is not doing anything. I am missing something easy,
thanks for your help.