Contract dates due to expire within 3 months Solved/Closed

Question

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

rizvisa1 · Answer

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

Ant · Answer

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 · Answer

Yup wrong formula. Check now.

Ant · Answer

By Joe i think youve got it, thanks!!!

Ant · Answer

Oh me again, can you add to the formula if contract date has expired, put X...

rizvisa1 · Answer

=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

Ant · Answer

Thanks very much, ur a Genius, :)

Ant · Answer

Hi rizvisa1,
Found another way which works also...

=IF(E1<=TODAY(),"Expired",IF(E1<=TODAY()+90,"Ending within 3 months","In Warranty "))

rizvisa1 · Answer

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.

Contract dates due to expire within 3 months

9 responses

Subscribe To Our Newsletter!