| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to do multiple IF checks with dates AND text
Hello All,
I am trying to get a formula set up that will look at multiple colums, and produce output in a particular column based on the checks made. It's a boolean thing; here's an example: In column A, I have dates in the format mm/dd/yy, in column B, I have text that can be one of four selections: Resolved, In Progress, Assigned, New. I need to compare column A against: 1. Today's date. If column A is earlier than today's date, AND column B is "Resolved", I need to set column C to "No." 2. Today's date. If column A is later than today's date, AND column B is anything but "Resolved", I need to set column C to "Yes". 3. Today's date. If column A is later than today's date, and column B is "Resolved", I need to set column C to "No". 4. Today's date. If column A is later than today's date, and column B is anything but "Resolved", I need to set column C to "No". I've tried multiple variations of the IF, AND, OR worksheet functions, and either it sets them all to the same thing (60 rows in column C) or gives me a zero in column C. Also thought of using VLOOKUP, but could not make that work either. Can sombody help me figure out the necessary cell formula that goes in column C, please? Thanks in advance! -- - RJF |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to do multiple IF checks with dates AND text
RJ Fleener wrote:
In column A, I have dates in the format mm/dd/yy, in column B, I have text that can be one of four selections: Resolved, In Progress, Assigned, New. I need to compare column A against: 1. Today's date. If column A is earlier than today's date, AND column B is "Resolved", I need to set column C to "No." 2. Today's date. If column A is later than today's date, AND column B is anything but "Resolved", I need to set column C to "Yes". 3. Today's date. If column A is later than today's date, and column B is "Resolved", I need to set column C to "No". 4. Today's date. If column A is later than today's date, and column B is anything but "Resolved", I need to set column C to "No". What if A is earlier than today's date AND B is not "Resolved"? I assume you want "No". =if(and(B1<"Resolved", A1today()), "Yes", "No") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to do multiple IF checks with dates AND text
1.
=IF(AND(A1<TODAY(),B1="Resolved"),"No",IF(AND(A1T ODAY(),B1<"Resolved"),"YES",IF(AND(A1TODAY(),B1= "Resolved"),"No",IF(AND(A1TODAY(),B1<"Resolved") ),"No","")))) Dave -- Brevity is the soul of wit. "RJ Fleener" wrote: Hello All, I am trying to get a formula set up that will look at multiple colums, and produce output in a particular column based on the checks made. It's a boolean thing; here's an example: In column A, I have dates in the format mm/dd/yy, in column B, I have text that can be one of four selections: Resolved, In Progress, Assigned, New. I need to compare column A against: 1. Today's date. If column A is earlier than today's date, AND column B is "Resolved", I need to set column C to "No." 2. Today's date. If column A is later than today's date, AND column B is anything but "Resolved", I need to set column C to "Yes". 3. Today's date. If column A is later than today's date, and column B is "Resolved", I need to set column C to "No". 4. Today's date. If column A is later than today's date, and column B is anything but "Resolved", I need to set column C to "No". I've tried multiple variations of the IF, AND, OR worksheet functions, and either it sets them all to the same thing (60 rows in column C) or gives me a zero in column C. Also thought of using VLOOKUP, but could not make that work either. Can sombody help me figure out the necessary cell formula that goes in column C, please? Thanks in advance! -- - RJF |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to do multiple IF checks with dates AND text
" wrote: RJ Fleener wrote: In column A, I have dates in the format mm/dd/yy, in column B, I have text that can be one of four selections: Resolved, In Progress, Assigned, New. I need to compare column A against: 1. Today's date. If column A is earlier than today's date, AND column B is "Resolved", I need to set column C to "No." 2. Today's date. If column A is later than today's date, AND column B is anything but "Resolved", I need to set column C to "Yes". 3. Today's date. If column A is later than today's date, and column B is "Resolved", I need to set column C to "No". 4. Today's date. If column A is later than today's date, and column B is anything but "Resolved", I need to set column C to "No". What if A is earlier than today's date AND B is not "Resolved"? I assume you want "No". =if(and(B1<"Resolved", A1today()), "Yes", "No") Yes, that is correct. I guess I just forgot that one. I will give this a go and see if it works...thanks! -- RJF |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to do multiple IF checks with dates AND text
"Dave F" wrote: 1. =IF(AND(A1<TODAY(),B1="Resolved"),"No",IF(AND(A1T ODAY(),B1<"Resolved"),"YES",IF(AND(A1TODAY(),B1= "Resolved"),"No",IF(AND(A1TODAY(),B1<"Resolved") ),"No","")))) Dave -- Brevity is the soul of wit. "RJ Fleener" wrote: Hello All, I am trying to get a formula set up that will look at multiple colums, and produce output in a particular column based on the checks made. It's a boolean thing; here's an example: In column A, I have dates in the format mm/dd/yy, in column B, I have text that can be one of four selections: Resolved, In Progress, Assigned, New. I need to compare column A against: 1. Today's date. If column A is earlier than today's date, AND column B is "Resolved", I need to set column C to "No." 2. Today's date. If column A is later than today's date, AND column B is anything but "Resolved", I need to set column C to "Yes". 3. Today's date. If column A is later than today's date, and column B is "Resolved", I need to set column C to "No". 4. Today's date. If column A is later than today's date, and column B is anything but "Resolved", I need to set column C to "No". I've tried multiple variations of the IF, AND, OR worksheet functions, and either it sets them all to the same thing (60 rows in column C) or gives me a zero in column C. Also thought of using VLOOKUP, but could not make that work either. Can sombody help me figure out the necessary cell formula that goes in column C, please? Thanks in advance! -- - RJF I'll give this a try and see if it works out...thanks! -- - RJF |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to do multiple IF checks with dates AND text
"RJ Fleener" wrote: " wrote: RJ Fleener wrote: In column A, I have dates in the format mm/dd/yy, in column B, I have text that can be one of four selections: Resolved, In Progress, Assigned, New. I need to compare column A against: 1. Today's date. If column A is earlier than today's date, AND column B is "Resolved", I need to set column C to "No." 2. Today's date. If column A is later than today's date, AND column B is anything but "Resolved", I need to set column C to "Yes". 3. Today's date. If column A is later than today's date, and column B is "Resolved", I need to set column C to "No". 4. Today's date. If column A is later than today's date, and column B is anything but "Resolved", I need to set column C to "No". What if A is earlier than today's date AND B is not "Resolved"? I assume you want "No". =if(and(B1<"Resolved", A1today()), "Yes", "No") Yes, that is correct. I guess I just forgot that one. I will give this a go and see if it works...thanks! -- RJF Follow-up: Your solution works like it should; thanks for the assist! -- - RJF |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to do multiple IF checks with dates AND text
"RJ Fleener" wrote: "Dave F" wrote: 1. =IF(AND(A1<TODAY(),B1="Resolved"),"No",IF(AND(A1T ODAY(),B1<"Resolved"),"YES",IF(AND(A1TODAY(),B1= "Resolved"),"No",IF(AND(A1TODAY(),B1<"Resolved") ),"No","")))) Dave -- Brevity is the soul of wit. "RJ Fleener" wrote: Hello All, I am trying to get a formula set up that will look at multiple colums, and produce output in a particular column based on the checks made. It's a boolean thing; here's an example: In column A, I have dates in the format mm/dd/yy, in column B, I have text that can be one of four selections: Resolved, In Progress, Assigned, New. I need to compare column A against: 1. Today's date. If column A is earlier than today's date, AND column B is "Resolved", I need to set column C to "No." 2. Today's date. If column A is later than today's date, AND column B is anything but "Resolved", I need to set column C to "Yes". 3. Today's date. If column A is later than today's date, and column B is "Resolved", I need to set column C to "No". 4. Today's date. If column A is later than today's date, and column B is anything but "Resolved", I need to set column C to "No". I've tried multiple variations of the IF, AND, OR worksheet functions, and either it sets them all to the same thing (60 rows in column C) or gives me a zero in column C. Also thought of using VLOOKUP, but could not make that work either. Can sombody help me figure out the necessary cell formula that goes in column C, please? Thanks in advance! -- - RJF I'll give this a try and see if it works out...thanks! -- - RJF Follow-up: Your solution works; thanks for the assist! -- - RJF |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format text 'dates' to real dates | Excel Worksheet Functions | |||
| Converting numbers stored as dates to text in Excel | Excel Discussion (Misc queries) | |||
| Multiple dates, times, or formulas in a single cell | Excel Discussion (Misc queries) | |||
| dates and text | Excel Discussion (Misc queries) | |||
| COUNTIF With Multiple Dates, Columns and Text | Excel Worksheet Functions | |||
Linear Mode
