Contract dates due to expire within 3 months

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

In Excel 2003, I have a list of all contracts with expiry dates. I need to add a colum entitled Due to run out in next 3 months. How do I calculate which contracts are due to expire in next 3 months, adding "Y" or "N" as a text. I believe its an IF statement, but I cant work it out and I have already spent too long trying to figure it out...

E.g.
Column I Column J Column K
Date Shipped Date Expires Due to Expire in next 3 Months Y/ N
03/07/2000 02/07/2005 N (Formula to return a Y or N
11/05/2005 10/05/2010 N
27/05/2005 27/05/2010 Y



Thanks

Ant

9 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this

=IF(B2<TODAY(),"N",IF(B2>DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"N","Y"))

Says for value in B2

if b2 is less than today's date, then a "N"
if b2 is more than 3 months from today then a "N"

if not both, then a Y
Thanks this partly solves it, however i also have contract dates in the future i.e 17/05/2011 but i dont want these future dates to show up as Y, and want them to show up as N... As i only want to chase contracts ending within 3 months... Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Yup wrong formula. Check now.
By Joe i think youve got it, thanks!!!
Oh me again, can you add to the formula if contract date has expired, put X...
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
=IF(B2<TODAY(),"X",IF(B2>DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"N","Y"))

Says for value in B2

if b2 is less than today's date, then a "X"
if b2 is more than 3 months from today then a "N"

if not both, then a Y
Thanks very much, ur a Genius, :)
Hi rizvisa1,
Found another way which works also...

=IF(E1<=TODAY(),"Expired",IF(E1<=TODAY()+90,"Ending within 3 months","In Warranty "))
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It may or may not work for you. It is adding 90 days, so months those are not 30 days are not accounted for. It you had meant 90 days this will work and mine will not without a change. And if you meant 3 months then this will not work and mine will

Let me explain both to you
=IF(E1<=TODAY(),"Expired",IF(E1<=TODAY()+90,"Ending within 3 months","In Warranty "))
It first check if the value in E1 is less than or equal today's date [ E1<=TODAY() ]
if E1 is less or equal today's date, it shows "Expired"
So far you have established that E1 is greater than today or not.
Next IF statement is checking if value in E1 is in less than or equal to 90 days from today [ (E1<=TODAY()+90 ]. If that is the case, it shows "ending within 3 months". For the third possibility where the date is more than 90 days out from today, it shows in Warranty

=IF(B2<TODAY(),"X",IF(B2>DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"N","Y"))
This one is says
if B2 is less than today [ B2<TODAY() ], then show me an X
If that is not the case then it makes another check. It takes today's date and split it into its component of year [YEAR(TODAY())], month [MONTH(TODAY())] and days [DAY(TODAY()). To the month part, it add 3 [ MONTH(TODAY())+3 ]. So essentially, it adds 3 months today's date and compare it against the date in b2. If the date three months from today is greater than more than three months out, it shows "N", It the date falls within 3 months it shows Y

Hope it help.