Errata....

I should have written EDATE, not EOMONTH.


----- original message -----

"JoeU2004" wrote in message
...
"Torfinn Brokke" wrote:
I want to be able to put in a date and
have the form return "VALID" if today is more than two months before the
expiry date, "PENDING" if today is less than two months before the expiry
date, and "EXPIRED" if today has passed the expiry date.
[....]
=IF(E6$D$4;"VALID";IF(E6$D$4-2;"PENDING";IF(E6<$D$4;"EXPIRED")))
(D4 contains the "TODAY()" function, and E6 is the date

If E6 contains a complete date (year, month, day) just as D4 does, then
the first part of your formula (E6D4) fails to take the two-month
criterion into account, the second part (E6D4-2) is subtracting 2
__days__, not 2 months. The correct formula to use is"

=if($D$4<EOMONTH(E6;-2);"VALID";if($D$4E6;"EXPIRED";"PENDING"))

Note: This returns "PENDING" if today is less than __or_equal__ to two
months before the expiration date.

If you get a #NAME error, see the help page for EOMONTH. If you cannot or
do not want to load the Analysis ToolPak, replace EOMONTH(E6;-2) with
DATE(year(E6);month(E6)-2;day(E6)).

I have not checked to see how the suggested formula behaves when today or
the expiration date is Feb 29 or the 31st of some month.


----- original message -----

"Torfinn Brokke" wrote in
message ...
Hello!

I've got a question for you Excel experts out there. I'm trying to make a
form to keep track of expiry dates. I want to be able to put in a date
and
have the form return "VALID" if today is more than two months before the
expiry date, "PENDING" if today is less than two months before the expiry
date, and "EXPIRED" if today has passed the expiry date.

I've been using the following function for this:

=IF(E6$D$4;"VALID";IF(E6$D$4-2;"PENDING";IF(E6<$D$4;"EXPIRED")))

(D4 contains the "TODAY()" function, and E6 is the date for the item that
I've put in manually plus the number of months until expiry for the given
item.)

This works for "VALID" and "EXPIRED", but not for "PENDING". However,
until
earlier today it worked perfectly, but when I put up the exact same
formula
after rearranging some cells, I can't get it to give the output
"PENDING".

It would be much appreciated if somebody could help me figure out what is
wrong.

Thanks in advance!