Termination within number of days
 

What is the formula to show terminations that falls under 30,60,90,180 and 365+

Column A is Term Date Column B should be the number of day the term falls
under
A B

Termination Date Catagory
06/17/2009 =180
10/01/2009 =60
02/21/2009 (need formula to show this)
05/19/2009
09/15/2009
01/07/2009
05/31/2009
03/02/2009

Termination within number of days
 

"the number of day the term falls under" what?
If you mean from today, you want something like:
=if(today()-a2365,"365+",if(today()-a2=180,"=180",if(today()-a2=90,"=90",if(today()-a2=60,"=60",if(today()-a2=30,"=30","Current")))))

Regards,
Fred

"Aging Termination Dates" <Aging Termination
wrote in message
...
What is the formula to show terminations that falls under 30,60,90,180 and
365+

Column A is Term Date Column B should be the number of day the term falls
under
A B

Termination Date Catagory
06/17/2009 =180
10/01/2009 =60
02/21/2009 (need formula to show this)
05/19/2009
09/15/2009
01/07/2009
05/31/2009
03/02/2009

Termination within number of days
 

Try
="=" & LOOKUP(TODAY()-A2,{30,60,90,180,365},{30,60,90,180,365})

OR
="" & DATEDIF(A2,TODAY(),"m")*30

--
Jacob


"Aging Termination Dates" wrote:

What is the formula to show terminations that falls under 30,60,90,180 and 365+

Column A is Term Date Column B should be the number of day the term falls
under
A B

Termination Date Catagory
06/17/2009 =180
10/01/2009 =60
02/21/2009 (need formula to show this)
05/19/2009
09/15/2009
01/07/2009
05/31/2009
03/02/2009

Termination within number of days
 

Since the lookup array and result array are same you can use the below for
the 1st formula

="=" & LOOKUP(TODAY()-A2,{30,60,90,180,365})

--
Jacob


"Jacob Skaria" wrote:

Try
="=" & LOOKUP(TODAY()-A2,{30,60,90,180,365},{30,60,90,180,365})

OR
="" & DATEDIF(A2,TODAY(),"m")*30

--
Jacob


"Aging Termination Dates" wrote:

What is the formula to show terminations that falls under 30,60,90,180 and 365+

Column A is Term Date Column B should be the number of day the term falls
under
A B

Termination Date Catagory
06/17/2009 =180
10/01/2009 =60
02/21/2009 (need formula to show this)
05/19/2009
09/15/2009
01/07/2009
05/31/2009
03/02/2009

Termination within number of days
 

Paste this formula in B1 cell.

=IF(AND(DATEDIF(A1,TODAY(),"D")=30,DATEDIF(A1,TOD AY(),"D")<60),"=30",IF(AND(DATEDIF(A1,TODAY(),"D" )=60,DATEDIF(A1,TODAY(),"D")<90),"=60",IF(AND(DA TEDIF(A1,TODAY(),"D")=90,DATEDIF(A1,TODAY(),"D")< 180),"=90",IF(AND(DATEDIF(A1,TODAY(),"D")=180,DA TEDIF(A1,TODAY(),"D")<365),"=180",IF(DATEDIF(A1,T ODAY(),"D")=365,"365+","")))))

Copy the B1 cell and apply it for the remaining cells of B Column

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Aging Termination Dates" wrote:

What is the formula to show terminations that falls under 30,60,90,180 and 365+

Column A is Term Date Column B should be the number of day the term falls
under
A B

Termination Date Catagory
06/17/2009 =180
10/01/2009 =60
02/21/2009 (need formula to show this)
05/19/2009
09/15/2009
01/07/2009
05/31/2009
03/02/2009

Termination within number of days
 

You can shorten and simnplify that by replacing each occurrence of
DATEDIF(A1,TODAY(),"D")
by the simpler function
TODAY()-A1

That shortens
=IF(AND(DATEDIF(A1,TODAY(),"D")=30,DATEDIF(A1,TOD AY(),"D")<60),"=30",IF(AND(DATEDIF(A1,TODAY(),"D" )=60,DATEDIF(A1,TODAY(),"D")<90),"=60",IF(AND(DA TEDIF(A1,TODAY(),"D")=90,DATEDIF(A1,TODAY(),"D")< 180),"=90",IF(AND(DATEDIF(A1,TODAY(),"D")=180,DA TEDIF(A1,TODAY(),"D")<365),"=180",IF(DATEDIF(A1,T ODAY(),"D")=365,"365+","")))))
to
=IF(AND(TODAY()-A1=30,TODAY()-A1<60),"=30",IF(AND(TODAY()-A1=60,TODAY()-A1<90),"=60",IF(AND(TODAY()-A1=90,TODAY()-A1<180),"=90",IF(AND(TODAY()-A1=180,TODAY()-A1<365),"=180",IF(TODAY()-A1=365,"365+","")))))
--
David Biddulph

"Ms-Exl-Learner" wrote in message
...
Paste this formula in B1 cell.

=IF(AND(DATEDIF(A1,TODAY(),"D")=30,DATEDIF(A1,TOD AY(),"D")<60),"=30",IF(AND(DATEDIF(A1,TODAY(),"D" )=60,DATEDIF(A1,TODAY(),"D")<90),"=60",IF(AND(DA TEDIF(A1,TODAY(),"D")=90,DATEDIF(A1,TODAY(),"D")< 180),"=90",IF(AND(DATEDIF(A1,TODAY(),"D")=180,DA TEDIF(A1,TODAY(),"D")<365),"=180",IF(DATEDIF(A1,T ODAY(),"D")=365,"365+","")))))

Copy the B1 cell and apply it for the remaining cells of B Column

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Aging Termination Dates" wrote:

What is the formula to show terminations that falls under 30,60,90,180
and 365+

Column A is Term Date Column B should be the number of day the term falls
under
A B

Termination Date Catagory
06/17/2009 =180
10/01/2009 =60
02/21/2009 (need formula to show this)
05/19/2009
09/15/2009
01/07/2009
05/31/2009
03/02/2009

Termination within number of days
 

Yes David Sir, Thank you for directing me in the right way.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"David Biddulph" wrote:

You can shorten and simnplify that by replacing each occurrence of
DATEDIF(A1,TODAY(),"D")
by the simpler function
TODAY()-A1

That shortens
=IF(AND(DATEDIF(A1,TODAY(),"D")=30,DATEDIF(A1,TOD AY(),"D")<60),"=30",IF(AND(DATEDIF(A1,TODAY(),"D" )=60,DATEDIF(A1,TODAY(),"D")<90),"=60",IF(AND(DA TEDIF(A1,TODAY(),"D")=90,DATEDIF(A1,TODAY(),"D")< 180),"=90",IF(AND(DATEDIF(A1,TODAY(),"D")=180,DA TEDIF(A1,TODAY(),"D")<365),"=180",IF(DATEDIF(A1,T ODAY(),"D")=365,"365+","")))))
to
=IF(AND(TODAY()-A1=30,TODAY()-A1<60),"=30",IF(AND(TODAY()-A1=60,TODAY()-A1<90),"=60",IF(AND(TODAY()-A1=90,TODAY()-A1<180),"=90",IF(AND(TODAY()-A1=180,TODAY()-A1<365),"=180",IF(TODAY()-A1=365,"365+","")))))
--
David Biddulph

"Ms-Exl-Learner" wrote in message
...
Paste this formula in B1 cell.

=IF(AND(DATEDIF(A1,TODAY(),"D")=30,DATEDIF(A1,TOD AY(),"D")<60),"=30",IF(AND(DATEDIF(A1,TODAY(),"D" )=60,DATEDIF(A1,TODAY(),"D")<90),"=60",IF(AND(DA TEDIF(A1,TODAY(),"D")=90,DATEDIF(A1,TODAY(),"D")< 180),"=90",IF(AND(DATEDIF(A1,TODAY(),"D")=180,DA TEDIF(A1,TODAY(),"D")<365),"=180",IF(DATEDIF(A1,T ODAY(),"D")=365,"365+","")))))

Copy the B1 cell and apply it for the remaining cells of B Column

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Aging Termination Dates" wrote:

What is the formula to show terminations that falls under 30,60,90,180
and 365+

Column A is Term Date Column B should be the number of day the term falls
under
A B

Termination Date Catagory
06/17/2009 =180
10/01/2009 =60
02/21/2009 (need formula to show this)
05/19/2009
09/15/2009
01/07/2009
05/31/2009
03/02/2009



.