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

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

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