Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple if statements based on two columns of information
I have written the following to the best of my ability but it isn't working.
Can anyone help me figure out what I'm doing wrong? E, G, H, I are givens and I want it to caluclate K based them. The deadline (K) is whichever is sooner the notice date (H) or G/I minus 105days. I wrote the four situations/outcomes below to try to illustrate it. =IF(AND(E20="CANCELLATION",H20<=DATE (YEAR(I20), MONTH(I20), DAY(I20)-105))), H20, IF(AND (E20="CANCELLATION",H20 DATE (YEAR(I20), MONTH(I20), DAY(I20)-105))), DATE(YEAR(I20),MONTH(I20),DAY(I20)-105), DATE(YEAR(G20),MONTH(G20),DAY(G20)-105))) A ... E ... G H I K Bldg Notice Exp Notice Effect Deadline Number Type Date Date Date Date (-105) 20 Cancel 06/30/2011 06/01/2009 07/01/2009 I-105days 21 Cancel 06/30/2011 06/30/2009 07/01/2010 H 22 Other 11/30/2009 10/30/2009 12/01/2009 G-105days 23 Other 11/30/2009 01/30/2009 12/01/2009 H Thank you in advance for your assistance. Elizabeth |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple if statements based on two columns of information
Just reading what you are looking for, and not paying attention to the Cancel
(or Cancellation in the formula), or the fact that if you have a date in the first 3 1/2 months of the year that your DAY(I20)-105 is going to error, the way I read it, you are looking for the earliest of 3 dates. The 3 dates being, the Notice Date, the Effect Date (minus 105 days), and the Exp Date (minus 105 days). So long as all dates are entered as dates (and not formatted as text), you could type the following in K20: =MIN(H20,G20-105,I20-105) And be sure it is formatted as a date. -- John C "Elizabeth" wrote: I have written the following to the best of my ability but it isn't working. Can anyone help me figure out what I'm doing wrong? E, G, H, I are givens and I want it to caluclate K based them. The deadline (K) is whichever is sooner the notice date (H) or G/I minus 105days. I wrote the four situations/outcomes below to try to illustrate it. =IF(AND(E20="CANCELLATION",H20<=DATE (YEAR(I20), MONTH(I20), DAY(I20)-105))), H20, IF(AND (E20="CANCELLATION",H20 DATE (YEAR(I20), MONTH(I20), DAY(I20)-105))), DATE(YEAR(I20),MONTH(I20),DAY(I20)-105), DATE(YEAR(G20),MONTH(G20),DAY(G20)-105))) A ... E ... G H I K Bldg Notice Exp Notice Effect Deadline Number Type Date Date Date Date (-105) 20 Cancel 06/30/2011 06/01/2009 07/01/2009 I-105days 21 Cancel 06/30/2011 06/30/2009 07/01/2010 H 22 Other 11/30/2009 10/30/2009 12/01/2009 G-105days 23 Other 11/30/2009 01/30/2009 12/01/2009 H Thank you in advance for your assistance. Elizabeth |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple if statements based on two columns of information
Geez, that is way easier that my complicated if statements. I knew someone
would be able to help me. One more thing... Now that I have the column for 105 days, I also need to know how to make another column that is 18 months out instead of 105 days out. How does the =min function work with months instead of days? "John C" wrote: Just reading what you are looking for, and not paying attention to the Cancel (or Cancellation in the formula), or the fact that if you have a date in the first 3 1/2 months of the year that your DAY(I20)-105 is going to error, the way I read it, you are looking for the earliest of 3 dates. The 3 dates being, the Notice Date, the Effect Date (minus 105 days), and the Exp Date (minus 105 days). So long as all dates are entered as dates (and not formatted as text), you could type the following in K20: =MIN(H20,G20-105,I20-105) And be sure it is formatted as a date. -- John C "Elizabeth" wrote: I have written the following to the best of my ability but it isn't working. Can anyone help me figure out what I'm doing wrong? E, G, H, I are givens and I want it to caluclate K based them. The deadline (K) is whichever is sooner the notice date (H) or G/I minus 105days. I wrote the four situations/outcomes below to try to illustrate it. =IF(AND(E20="CANCELLATION",H20<=DATE (YEAR(I20), MONTH(I20), DAY(I20)-105))), H20, IF(AND (E20="CANCELLATION",H20 DATE (YEAR(I20), MONTH(I20), DAY(I20)-105))), DATE(YEAR(I20),MONTH(I20),DAY(I20)-105), DATE(YEAR(G20),MONTH(G20),DAY(G20)-105))) A ... E ... G H I K Bldg Notice Exp Notice Effect Deadline Number Type Date Date Date Date (-105) 20 Cancel 06/30/2011 06/01/2009 07/01/2009 I-105days 21 Cancel 06/30/2011 06/30/2009 07/01/2010 H 22 Other 11/30/2009 10/30/2009 12/01/2009 G-105days 23 Other 11/30/2009 01/30/2009 12/01/2009 H Thank you in advance for your assistance. Elizabeth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statements and multiple columns | Excel Worksheet Functions | |||
Count if multiple statements in two columns | Excel Worksheet Functions | |||
Sum column information based on multiple criteria | Excel Worksheet Functions | |||
combining IF and AND statements for multiple columns | Excel Discussion (Misc queries) | |||
Looking up information based on Columns and Rows | Excel Worksheet Functions |