If formula with text string help please
 

In column C ..... I would like to ask the question....if A2=1 and B2 which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due

If formula with text string help please
 

ferde wrote:
In column C ..... I would like to ask the question....if A2=1 and B2 which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due


If you can assume B /always/ has a 4-digit year on the right this should
work in C1. Fill down as needed:

=IF(A1=1,IF(VALUE(RIGHT(TRIM(B1),4))2003,"vaccine given","vaccine
due"),"na")

Allowance has been made for trailing spaces in B.

If formula with text string help please
 

Thank you works like a charm

"smartin" wrote:

ferde wrote:
In column C ..... I would like to ask the question....if A2=1 and B2 which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due


If you can assume B /always/ has a 4-digit year on the right this should
work in C1. Fill down as needed:

=IF(A1=1,IF(VALUE(RIGHT(TRIM(B1),4))2003,"vaccine given","vaccine
due"),"na")

Allowance has been made for trailing spaces in B.

If formula with text string help please
 

The formula you gave me works great but I forgot to consider that B2 may
be empty and so now I get a value error using the formula you suggested
because there is no date to calculate...is there an easy fix for this ?

So if A2 =1 and B2 is empty ...then C 2 = Vaccine due

Thank you again for your help

"ferde" wrote:

In column C ..... I would like to ask the question....if A2=1 and B2 which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due

If formula with text string help please
 

Was this directed at me?

Try this in C2 (not C1 as before):

=IF((A2=1)*(B2<""),IF(VALUE(RIGHT(TRIM(B2),4))20 03,"vaccine
given","vaccine due"),"na")


ferde wrote:
The formula you gave me works great but I forgot to consider that B2 may
be empty and so now I get a value error using the formula you suggested
because there is no date to calculate...is there an easy fix for this ?

So if A2 =1 and B2 is empty ...then C 2 = Vaccine due

Thank you again for your help

"ferde" wrote:

In column C ..... I would like to ask the question....if A2=1 and B2 which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due

If formula with text string help please
 

Smartin... I plugged in the new formula into C2 but it gives me "na" as a
result but it should read "vaccine due" because the value in A2=1

am I doing something wrong?



"smartin" wrote:

Was this directed at me?

Try this in C2 (not C1 as before):

=IF((A2=1)*(B2<""),IF(VALUE(RIGHT(TRIM(B2),4))20 03,"vaccine
given","vaccine due"),"na")


ferde wrote:
The formula you gave me works great but I forgot to consider that B2 may
be empty and so now I get a value error using the formula you suggested
because there is no date to calculate...is there an easy fix for this ?

So if A2 =1 and B2 is empty ...then C 2 = Vaccine due

Thank you again for your help

"ferde" wrote:

In column C ..... I would like to ask the question....if A2=1 and B2 which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due

If formula with text string help please
 

=IF(A2<1,"","Vacine " &
IF(AND(A2=1,VALUE(MID(B2,FIND("/",B2)+4,4))2003),"Given","due"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ferde" wrote in message
...
In column C ..... I would like to ask the question....if A2=1 and B2
which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due

If formula with text string help please
 

Thank you Don.... very much appreciated

"Don Guillett" wrote:

=IF(A2<1,"","Vacine " &
IF(AND(A2=1,VALUE(MID(B2,FIND("/",B2)+4,4))2003),"Given","due"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ferde" wrote in message
...
In column C ..... I would like to ask the question....if A2=1 and B2
which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due

If formula with text string help please
 

Sorry about that. Try this instead in A2 and fill down:

=IF(A2=0,"na",IF((A2=1)*(IF(ISERROR(VALUE(RIGHT(B2 ,4))),0,VALUE(RIGHT(B2,4)))2003),"vaccine
given","vaccine due"))

Result 1:

1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due

Result 2:

1 <blank vaccine due
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due


ferde wrote:
Smartin... I plugged in the new formula into C2 but it gives me "na" as a
result but it should read "vaccine due" because the value in A2=1

am I doing something wrong?



"smartin" wrote:

Was this directed at me?

Try this in C2 (not C1 as before):

=IF((A2=1)*(B2<""),IF(VALUE(RIGHT(TRIM(B2),4))20 03,"vaccine
given","vaccine due"),"na")


ferde wrote:
The formula you gave me works great but I forgot to consider that B2 may
be empty and so now I get a value error using the formula you suggested
because there is no date to calculate...is there an easy fix for this ?

So if A2 =1 and B2 is empty ...then C 2 = Vaccine due

Thank you again for your help

"ferde" wrote:

In column C ..... I would like to ask the question....if A2=1 and B2 which
is formatted as "general" has a year date in its string which is 2003 the
answer = "vaccine given" but if the year date is <2003 then the answer
="vaccine due". Also I should add if A2 is 0 then the answer =na.

I'm not sure how to proceed and any help would be appreciated.

A B C
1 HIST-Pnuemo-10/01/2007 vaccine given
0 HIST-Pnuemo-07/24/2009 na
1 HIST-Pnuemo-01/01/2003 vaccine due