I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

in U1
=if(or(F1=1,F1=2,F1=3),N1+16,if(or(F1=4,F1=5),"N/A","otherwise"))
if f1 can only be 1-5 then
=if(f1<=3,N1+16,"N/A")
if you want the N/A error then replace "N/A" with NA()

"Karen" wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

Assuming N1 as a date

On cell U1 write:
=if(or(f1=1,f1=2,f1=3),n1+16,if(or(f1=4,f1=5),"N/A","not defined"))

This will work as requested and will write "not defined" for values
different than 1,2,3,4,5.

Let me know how it works.

Greetings

Leo.

"Karen" wrote in message
...
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

Assuming N1 as a date

On cell U1 write:
=if(or(f1=1,f1=2,f1=3),n1+16,if(or(f1=4,f1=5),"N/A","not defined"))

This will work as requested and will write "not defined" for values
different than 1,2,3,4,5.

Let me know how it works.

Greetings

Leo.

"Karen" wrote in message
...
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

On Wed, 8 Aug 2007 10:58:01 -0700, Karen
wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

U1: =N1+HLOOKUP(F1,{1,2,3;16,16,16},2,0)


--ron

On Wed, 8 Aug 2007 10:58:01 -0700, Karen
wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

U1: =N1+HLOOKUP(F1,{1,2,3;16,16,16},2,0)


--ron

Thank you VERY much for your help.

"Ron Rosenfeld" wrote:

On Wed, 8 Aug 2007 10:58:01 -0700, Karen
wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

U1: =N1+HLOOKUP(F1,{1,2,3;16,16,16},2,0)


--ron

Thank you VERY much for your help.

"Ron Rosenfeld" wrote:

On Wed, 8 Aug 2007 10:58:01 -0700, Karen
wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

U1: =N1+HLOOKUP(F1,{1,2,3;16,16,16},2,0)


--ron

Thank you VERY much for your help.

"Leo Rod" wrote:

Assuming N1 as a date

On cell U1 write:
=if(or(f1=1,f1=2,f1=3),n1+16,if(or(f1=4,f1=5),"N/A","not defined"))

This will work as requested and will write "not defined" for values
different than 1,2,3,4,5.

Let me know how it works.

Greetings

Leo.

"Karen" wrote in message
...
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

Thank you VERY much for your help.

"Leo Rod" wrote:

Assuming N1 as a date

On cell U1 write:
=if(or(f1=1,f1=2,f1=3),n1+16,if(or(f1=4,f1=5),"N/A","not defined"))

This will work as requested and will write "not defined" for values
different than 1,2,3,4,5.

Let me know how it works.

Greetings

Leo.

"Karen" wrote in message
...
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

Thank you VERY much for your help.

"bj" wrote:

in U1
=if(or(F1=1,F1=2,F1=3),N1+16,if(or(F1=4,F1=5),"N/A","otherwise"))
if f1 can only be 1-5 then
=if(f1<=3,N1+16,"N/A")
if you want the N/A error then replace "N/A" with NA()

"Karen" wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

Thank you VERY much for your help.

"bj" wrote:

in U1
=if(or(F1=1,F1=2,F1=3),N1+16,if(or(F1=4,F1=5),"N/A","otherwise"))
if f1 can only be 1-5 then
=if(f1<=3,N1+16,"N/A")
if you want the N/A error then replace "N/A" with NA()

"Karen" wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen

"Karen" wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen

"Karen" wrote:

I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen

Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()

then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete


On Aug 8, 10:16 pm, Karen wrote:
Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen



"Karen" wrote:
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen- Hide quoted text -

- Show quoted text -

Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()

then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete


On Aug 8, 10:16 pm, Karen wrote:
Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen



"Karen" wrote:
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen- Hide quoted text -

- Show quoted text -

Thank you VERY much for your help.


"Pete_UK" wrote:

Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()

then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete


On Aug 8, 10:16 pm, Karen wrote:
Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen



"Karen" wrote:
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen- Hide quoted text -

- Show quoted text -

You're welcome, Karen - thanks for feeding back.

Pete

On Aug 9, 2:36 pm, Karen wrote:
Thank you VERY much for your help.



"Pete_UK" wrote:
Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()

then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete

On Aug 8, 10:16 pm, Karen wrote:
Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen

"Karen" wrote:
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks again Pete - If you could help me with one ore conditional
formatting. I have a date in cell N1 and the conditional formatting is set
to turn red if it's <= Today - Like you just helped me with. If I enter a
date in cell O1, I want the date in cell N1 to change back from red to black.
How can this be done?

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:

You're welcome, Karen - thanks for feeding back.

Pete

On Aug 9, 2:36 pm, Karen wrote:
Thank you VERY much for your help.



"Pete_UK" wrote:
Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()

then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete

On Aug 8, 10:16 pm, Karen wrote:
Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen

"Karen" wrote:
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I think you mean that if there is a date in O1 (any date?) then this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete

On Aug 9, 9:24 pm, Karen wrote:
Thanks again Pete - If you could help me with one ore conditional
formatting. I have a date in cell N1 and the conditional formatting is set
to turn red if it's <= Today - Like you just helped me with. If I enter a
date in cell O1, I want the date in cell N1 to change back from red to black.
How can this be done?

ANY help would be greatly appreciated, Karen



"Pete_UK" wrote:
You're welcome, Karen - thanks for feeding back.

Pete

On Aug 9, 2:36 pm, Karen wrote:
Thank you VERY much for your help.

"Pete_UK" wrote:
Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()

then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete

On Aug 8, 10:16 pm, Karen wrote:
Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen

"Karen" wrote:
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thank you again for your help and time. I must be doing something wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() - The date
will format to red
If I enter a date in column O, I want the date (it could be both black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen


"Pete_UK" wrote:

I think you mean that if there is a date in O1 (any date?) then this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete

On Aug 9, 9:24 pm, Karen wrote:
Thanks again Pete - If you could help me with one ore conditional
formatting. I have a date in cell N1 and the conditional formatting is set
to turn red if it's <= Today - Like you just helped me with. If I enter a
date in cell O1, I want the date in cell N1 to change back from red to black.
How can this be done?

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:
You're welcome, Karen - thanks for feeding back.

Pete

On Aug 9, 2:36 pm, Karen wrote:
Thank you VERY much for your help.

"Pete_UK" wrote:
Select Formula Is rather than Cell Contents Is in the CF panel, then
enter this formula:

=N1<=TODAY()
then click on the Format button and Patterns tab (for background
colour) and click on red, then OK your way out.

Hope this helps.

Pete

On Aug 8, 10:16 pm, Karen wrote:
Thank you everyone for your help. I want to add some conditional formatting
in column N. Let's say that if today's date is equal to or greater than the
date in cell N1, I want to change the background to red. I'm familiar with
changing the formatting; I just don't know what formula to use.

Thank you, Karen

"Karen" wrote:
I need help with an IF/THEN statement if anyone can be so kind.
The formula will be placed in column U.

In column F there could be a number 1, 2, 3, 4 or 5.
In column N will be a date.

This is the scenario:
If cell F1 has a 1, 2 or 3, then cell U1 equals N1 + 16 days.
If cell F1 has a 4 or 5, then I want an N/A in cell U1.

ANY help would be greatly appreciated, Karen- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -- Hide quoted text -

Show quoted text -

Hi Karen,

I've just tried it out and it seems to work how you want it, i.e.:

O1 empty, N1 less than or equal to today ---- red
O1 empty, N1 greater than today --- black
O1 not empty, N1 less than or equal to today --- black
O1 not empty, N1 greater than today --- black

This seems to be a correct interpretation of what you requested.

What's happening in your spreadsheet when you try the new formula with
the 4 variations of O1 and N1 listed above?

Pete

On Aug 10, 5:50 pm, Karen wrote:
Thank you again for your help and time. I must be doing something wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() - The date
will format to red
If I enter a date in column O, I want the date (it could be both black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:

I think you mean that if there is a date in O1 (any date?) then this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete

Thanks Pete - Someone I work with changed a formula.

This is what I have:
Column E: Can have a 1, 2, 3, 4, or 5
Column G: Date
Column N: CF Formula: ="AND(O1="""",N1<=TODAY())" ---- red
Formula in cell N1 ---- =IF(E2<4,SUM(G2+16),IF(L2="CSI",SUM(G2+16),"N/A"))
Column O = CF (Cell value is: =$P$2)

At this point, If I enter a date in column O, I want the date (it could be
both black or red) in column N to revert to or stay black.

Right now, column N is not turning red when it's <= today and column O is
blank.

Sorry, I'm confused



"Pete_UK" wrote:

Hi Karen,

I've just tried it out and it seems to work how you want it, i.e.:

O1 empty, N1 less than or equal to today ---- red
O1 empty, N1 greater than today --- black
O1 not empty, N1 less than or equal to today --- black
O1 not empty, N1 greater than today --- black

This seems to be a correct interpretation of what you requested.

What's happening in your spreadsheet when you try the new formula with
the 4 variations of O1 and N1 listed above?

Pete

On Aug 10, 5:50 pm, Karen wrote:
Thank you again for your help and time. I must be doing something wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() - The date
will format to red
If I enter a date in column O, I want the date (it could be both black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:

I think you mean that if there is a date in O1 (any date?) then this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete

You need to go in and edit the formula in the CF panel - you can see
that Excel (trying to be helpful) has adding some extra " symbols that
are not needed. Get rid of these and it should work how you want it
to.

Hope this helps.

Pete

On Aug 10, 9:10 pm, Karen wrote:
Thanks Pete - Someone I work with changed a formula.

This is what I have:
Column E: Can have a 1, 2, 3, 4, or 5
Column G: Date
Column N: CF Formula: ="AND(O1="""",N1<=TODAY())" ---- red
Formula in cell N1 ---- =IF(E2<4,SUM(G2+16),IF(L2="CSI",SUM(G2+16),"N/A"))
Column O = CF (Cell value is: =$P$2)

At this point, If I enter a date in column O, I want the date (it could be
both black or red) in column N to revert to or stay black.

Right now, column N is not turning red when it's <= today and column O is
blank.

Sorry, I'm confused



"Pete_UK" wrote:
Hi Karen,

I've just tried it out and it seems to work how you want it, i.e.:

O1 empty, N1 less than or equal to today ---- red
O1 empty, N1 greater than today --- black
O1 not empty, N1 less than or equal to today --- black
O1 not empty, N1 greater than today --- black

This seems to be a correct interpretation of what you requested.

What's happening in your spreadsheet when you try the new formula with
the 4 variations of O1 and N1 listed above?

Pete

On Aug 10, 5:50 pm, Karen wrote:
Thank you again for your help and time. I must be doing something wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() - The date
will format to red
If I enter a date in column O, I want the date (it could be both black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:

I think you mean that if there is a date in O1 (any date?) then this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete- Hide quoted text -

- Show quoted text -

Pete,

Please forgive my confusion. What extra symbols?

Thanks, Karen

"Pete_UK" wrote:

You need to go in and edit the formula in the CF panel - you can see
that Excel (trying to be helpful) has adding some extra " symbols that
are not needed. Get rid of these and it should work how you want it
to.

Hope this helps.

Pete

On Aug 10, 9:10 pm, Karen wrote:
Thanks Pete - Someone I work with changed a formula.

This is what I have:
Column E: Can have a 1, 2, 3, 4, or 5
Column G: Date
Column N: CF Formula: ="AND(O1="""",N1<=TODAY())" ---- red
Formula in cell N1 ---- =IF(E2<4,SUM(G2+16),IF(L2="CSI",SUM(G2+16),"N/A"))
Column O = CF (Cell value is: =$P$2)

At this point, If I enter a date in column O, I want the date (it could be
both black or red) in column N to revert to or stay black.

Right now, column N is not turning red when it's <= today and column O is
blank.

Sorry, I'm confused



"Pete_UK" wrote:
Hi Karen,

I've just tried it out and it seems to work how you want it, i.e.:

O1 empty, N1 less than or equal to today ---- red
O1 empty, N1 greater than today --- black
O1 not empty, N1 less than or equal to today --- black
O1 not empty, N1 greater than today --- black

This seems to be a correct interpretation of what you requested.

What's happening in your spreadsheet when you try the new formula with
the 4 variations of O1 and N1 listed above?

Pete

On Aug 10, 5:50 pm, Karen wrote:
Thank you again for your help and time. I must be doing something wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() - The date
will format to red
If I enter a date in column O, I want the date (it could be both black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:

I think you mean that if there is a date in O1 (any date?) then this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete- Hide quoted text -

- Show quoted text -

As Pete said, extra " symbols (quote marks).

You've got
="AND(O1="""",N1<=TODAY())"
where you should have
=AND(O1="",N1<=TODAY())
--
David Biddulph

"Karen" wrote in message
...
Pete,

Please forgive my confusion. What extra symbols?

Thanks, Karen

"Pete_UK" wrote:

You need to go in and edit the formula in the CF panel - you can see
that Excel (trying to be helpful) has adding some extra " symbols that
are not needed. Get rid of these and it should work how you want it
to.

Hope this helps.

Pete

On Aug 10, 9:10 pm, Karen wrote:
Thanks Pete - Someone I work with changed a formula.

This is what I have:
Column E: Can have a 1, 2, 3, 4, or 5
Column G: Date
Column N: CF Formula: ="AND(O1="""",N1<=TODAY())" ---- red
Formula in cell N1 ----
=IF(E2<4,SUM(G2+16),IF(L2="CSI",SUM(G2+16),"N/A"))
Column O = CF (Cell value is: =$P$2)

At this point, If I enter a date in column O, I want the date (it could
be
both black or red) in column N to revert to or stay black.

Right now, column N is not turning red when it's <= today and column O
is
blank.

Sorry, I'm confused



"Pete_UK" wrote:
Hi Karen,

I've just tried it out and it seems to work how you want it, i.e.:

O1 empty, N1 less than or equal to today ---- red
O1 empty, N1 greater than today --- black
O1 not empty, N1 less than or equal to today --- black
O1 not empty, N1 greater than today --- black

This seems to be a correct interpretation of what you requested.

What's happening in your spreadsheet when you try the new formula
with
the 4 variations of O1 and N1 listed above?

Pete

On Aug 10, 5:50 pm, Karen wrote:
Thank you again for your help and time. I must be doing something
wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() -
The date
will format to red
If I enter a date in column O, I want the date (it could be both
black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:

I think you mean that if there is a date in O1 (any date?) then
this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete- Hide quoted text -

- Show quoted text -

I'm sorry, but it still isn't working.

"David Biddulph" wrote:

As Pete said, extra " symbols (quote marks).

You've got
="AND(O1="""",N1<=TODAY())"
where you should have
=AND(O1="",N1<=TODAY())
--
David Biddulph

"Karen" wrote in message
...
Pete,

Please forgive my confusion. What extra symbols?

Thanks, Karen

"Pete_UK" wrote:

You need to go in and edit the formula in the CF panel - you can see
that Excel (trying to be helpful) has adding some extra " symbols that
are not needed. Get rid of these and it should work how you want it
to.

Hope this helps.

Pete

On Aug 10, 9:10 pm, Karen wrote:
Thanks Pete - Someone I work with changed a formula.

This is what I have:
Column E: Can have a 1, 2, 3, 4, or 5
Column G: Date
Column N: CF Formula: ="AND(O1="""",N1<=TODAY())" ---- red
Formula in cell N1 ----
=IF(E2<4,SUM(G2+16),IF(L2="CSI",SUM(G2+16),"N/A"))
Column O = CF (Cell value is: =$P$2)

At this point, If I enter a date in column O, I want the date (it could
be
both black or red) in column N to revert to or stay black.

Right now, column N is not turning red when it's <= today and column O
is
blank.

Sorry, I'm confused



"Pete_UK" wrote:
Hi Karen,

I've just tried it out and it seems to work how you want it, i.e.:

O1 empty, N1 less than or equal to today ---- red
O1 empty, N1 greater than today --- black
O1 not empty, N1 less than or equal to today --- black
O1 not empty, N1 greater than today --- black

This seems to be a correct interpretation of what you requested.

What's happening in your spreadsheet when you try the new formula
with
the 4 variations of O1 and N1 listed above?

Pete

On Aug 10, 5:50 pm, Karen wrote:
Thank you again for your help and time. I must be doing something
wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() -
The date
will format to red
If I enter a date in column O, I want the date (it could be both
black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:

I think you mean that if there is a date in O1 (any date?) then
this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete- Hide quoted text -

- Show quoted text -

Karen,

if you would like me to take a look at your file, send it to:

pashurst <at auditel.net

(change the obvious). Hopefully we can get to the bottom of this and
sort it out for you.

Pete

On Aug 14, 6:14 pm, Karen wrote:
I'm sorry, but it still isn't working.



"David Biddulph" wrote:
As Pete said, extra " symbols (quote marks).

You've got
="AND(O1="""",N1<=TODAY())"
where you should have
=AND(O1="",N1<=TODAY())
--
David Biddulph

"Karen" wrote in message
...
Pete,

Please forgive my confusion. What extra symbols?

Thanks, Karen

"Pete_UK" wrote:

You need to go in and edit the formula in the CF panel - you can see
that Excel (trying to be helpful) has adding some extra " symbols that
are not needed. Get rid of these and it should work how you want it
to.

Hope this helps.

Pete

On Aug 10, 9:10 pm, Karen wrote:
Thanks Pete - Someone I work with changed a formula.

This is what I have:
Column E: Can have a 1, 2, 3, 4, or 5
Column G: Date
Column N: CF Formula: ="AND(O1="""",N1<=TODAY())" ---- red
Formula in cell N1 ----
=IF(E2<4,SUM(G2+16),IF(L2="CSI",SUM(G2+16),"N/A"))
Column O = CF (Cell value is: =$P$2)

At this point, If I enter a date in column O, I want the date (it could
be
both black or red) in column N to revert to or stay black.

Right now, column N is not turning red when it's <= today and column O
is
blank.

Sorry, I'm confused

"Pete_UK" wrote:
Hi Karen,

I've just tried it out and it seems to work how you want it, i.e.:

O1 empty, N1 less than or equal to today ---- red
O1 empty, N1 greater than today --- black
O1 not empty, N1 less than or equal to today --- black
O1 not empty, N1 greater than today --- black

This seems to be a correct interpretation of what you requested.

What's happening in your spreadsheet when you try the new formula
with
the 4 variations of O1 and N1 listed above?

Pete

On Aug 10, 5:50 pm, Karen wrote:
Thank you again for your help and time. I must be doing something
wrong. I
used the formula you suggested and it's not working as planned.
The scenario is:
Column N contains a date
Right now there's CF in column N - The formula is: =N1<=TODAY() -
The date
will format to red
If I enter a date in column O, I want the date (it could be both
black or
red) in column N to revert to or stay black.

ANY help would be greatly appreciated, Karen

"Pete_UK" wrote:

I think you mean that if there is a date in O1 (any date?) then
this
will over-ride the other choice, but if O1 is empty you want the
comparison with TODAY as you already have it. If that is the case,
then try this as the formula:

=AND(O1="",N1<=TODAY())

Hope this helps.

Pete- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -