| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Text string in Sum formula | Excel Discussion (Misc queries) | |||
| Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
| Text String Formula Q | Excel Worksheet Functions | |||
| Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
| formula with text string | Excel Discussion (Misc queries) | |||
Linear Mode
