Problem with "IF" function

[Torfinn Brokke]

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!

 

[Pete_UK]

In subtracting 2 from D4, you are only subtracting 2 days. Change this
to 60 (or however many days you think there are in 2 months).

Hope this helps.

Pete

 

[Torfinn Brokke]

Hello, Pete!

Thank you very much! I just tried your suggestion, but for some reason it
still doesn't seem to work... Could there be something wrong with the formula
I've put in?


Best regards,
Torfinn

 

[JoeU2004]

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 (E6>D4) fails to take the two-month criterion
into account, the second part (E6>D4-2) is subtracting 2 __days__, not 2
months. The correct formula to use is"

=if($D$4<EOMONTH(E6;-2);"VALID";if($D$4>E6;"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]

Well, I just tested some more, and I just found out that it DOES work, but it
works the wrong way, i.e. it gives the output "PENDING" for the first 60 days
AFTER the expiry date, not before as it is supposed to.

What could be the reason for this?


Best regards,
Torfinn

 

[JoeU2004]

Errata....

I should have written EDATE, not EOMONTH.


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

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 (E6>D4) fails to take the two-month
criterion into account, the second part (E6>D4-2) is subtracting 2
__days__, not 2 months. The correct formula to use is"

=if($D$4<EOMONTH(E6;-2);"VALID";if($D$4>E6;"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 -----

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!

 

[p45cal]

try
=IF(E6>$D$4+60,"VALID",IF(E6>$D$4,"PENDING","EXPIRED")

 

[Torfinn Brokke]

JoeU2004,

Thank you very much! That seems to work like a charm!


Best regards,
Torfinn

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 (E6>D4) fails to take the two-month criterion
into account, the second part (E6>D4-2) is subtracting 2 __days__, not 2
months. The correct formula to use is"

=if($D$4<EOMONTH(E6;-2);"VALID";if($D$4>E6;"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.

 

[JoeU2004]

In subtracting 2 from D4, you are only subtracting 2 days.
Change this to 60 (or however many days you think there are
in 2 months).

I would not assume that "2 months" is the same a 60 days or any constant
number of days. However, that might depend on OP's intent and the laws of
the OP's jurisdiction.

In the US, "2 months" is not the same as "60 days" (or any other constant)
for legal purposes. "2 months" is usually defined in regulations as the
same day of the month or the end of the month if the same day does not
exist. For example, Feb 28 or Feb 29 is 2 months before Apr 30, depending
on the year. Generally, "2 months before" results in a difference of 59 to
62 days, with an average of 61 days. "2 months before" is 60 days in only 3
of 24 months (a normal year and a leap year).


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

In subtracting 2 from D4, you are only subtracting 2 days. Change this
to 60 (or however many days you think there are in 2 months).

Hope this helps.

Pete

 

[JoeU2004]

Errata....

Generally, "2 months before" results in a difference
of 59 to 62 days, with an average of 61 days.
"2 months before" is 60 days in only 3 of 24 months
(a normal year and a leap year).

More correctly, "2 months before" results in 59 to 62 days, with an average
of 60.92 days. "2 months before" is 60 days in 61 of 731 instances (8.34%)
in 2 years, a normal year and a leap year. "2 months before" is 59 days in
59 instances (8.07%), 61 days in 490 instances (67.03%), and 62 days in 121
instances (16.55%).


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