# Contract dates due to expire within 3 months

Solved/Closed
Ant - May 19, 2010 at 09:52 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 20, 2010 at 08:42 AM
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
Related:

## 9 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 19, 2010 at 10:04 AM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 19, 2010 at 10:36 AM
Yup wrong formula. Check now.
By Joe i think youve got it, thanks!!!

Didn't find the answer you are looking for?

Oh me again, can you add to the formula if contract date has expired, put X...
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 19, 2010 at 11:11 AM
=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 "))
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 20, 2010 at 08:42 AM
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.