Hello Excel Problem Gurus,

First of all, let me thank you in advance. I find it exemplary that
you all can devote time to helping others who are having issues with
their work. Hopefully one day I can be at a mentor level, and help
others too.

Hope you can help! I have an issue where I don't know how to write the
formula that I need without going over on the nesting. The current
formula that I have is as follows:

=IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No
Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/
A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W
$2,P7,"D")<30,"Yes","No")),IF(DATEDIF($W
$2,O7,"D")<30,"Yes","No")),IF(DATEDIF($W
$2,N7,"D")<30,"Yes","No")),IF(DATEDIF($W
$2,M7,"D")<30,"Yes","No")),IF(DATEDIF($W
$2,L7,"D")<30,"Yes","No")),IF(DATEDIF($W$2,J7,"D") <30,"Yes","No"))

This formula is in a column labeled as "Expiring in 30 Days?" and
should have a result of Yes or No for each row depending on if ANY of
the dates are inside of 30 days until expiration. Let me explain. In
this formula, Cell W2 contains the current date. Cell B7 is a count
cell, and should only be in the first part. Cells J7, L7, M7, N7, O7,
and P7 all contain expiration dates. This sheet was working until I
discovered that some of the data in these cells needed to also accept
N/A as a value. With just calcing if the DATEDIF was greater than/less
than 30 days we began getting VALUE errors. The previous working
formula was as follows:

=IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No
Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7, "D")<30,DATEDIF($W
$2,L7,"D")<30,DATEDIF($W$2,M7,"D")<30,DATEDIF($W
$2,N7,"D")<30,DATEDIF($W$2,O7,"D")<30,DATEDIF($W
$2,P7,"D")<30),"Yes","No")))


Basically I need the formula to ignore the cell if it has N/A, but not
stop the formula from checking the other cells in the row. There is
one more complicating factor. One cell, M7 we want to IGNORE
expiration dates and return a 'No' for that cell IF cell T7 has an "A"
in it.

Confused? I was too! So, all the error checking that's happening in
the above formula with the caveat that it need not flag M7 as expiring
in 30 days if cell T7="A". Ugh.

Any help would be appreciated. I've been stuck here for a few days,
and I feel like I'm overlooking something simple.

I'm thinking after reading on some forums (i.e.
http://www.computing.net/answers/off...help/7159.html)
that I'd need to have an array formula(e) ? I'm not sure how to work
with several of the functions that were listed in the example, so
hopefully someone here can help me adapt this over-nested formula. I'd
really like to keep it in the cell, and not need to jump to VBA if at
all possible.

I've posted a copy of one of the sheets based on this he
http://john.highoctane.net/Expiry.xls Hopefully being able to see the
conditional formatting that is based on this will be helpful too. I've
ran some mock numbers so you can play with the sheet and see how it
works. You'll see that it's mostly working, with the exception of the
formulae above located in column W. The specific issue is where we get
the #VALUE error.

Thanks for your assistance.
-John Phenom
(this is a valid email, just anticipating spam once
it's out on Usenet.. :)