Termination within number of days

  • Thread starter Aging Termination Dates
  • Start date
A

Aging Termination Dates

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
 
F

Fred Smith

"the number of day the term falls under" what?
If you mean from today, you want something like:
=if(today()-a2>365,"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
(e-mail address removed)> wrote in message
news:[email protected]...
 
J

Jacob Skaria

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

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

Jacob Skaria

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})
 
M

Ms-Exl-Learner

Paste this formula in B1 cell.

=IF(AND(DATEDIF(A1,TODAY(),"D")>=30,DATEDIF(A1,TODAY(),"D")<60),">=30",IF(AND(DATEDIF(A1,TODAY(),"D")>=60,DATEDIF(A1,TODAY(),"D")<90),">=60",IF(AND(DATEDIF(A1,TODAY(),"D")>=90,DATEDIF(A1,TODAY(),"D")<180),">=90",IF(AND(DATEDIF(A1,TODAY(),"D")>=180,DATEDIF(A1,TODAY(),"D")<365),">=180",IF(DATEDIF(A1,TODAY(),"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!
 
D

David Biddulph

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,TODAY(),"D")<60),">=30",IF(AND(DATEDIF(A1,TODAY(),"D")>=60,DATEDIF(A1,TODAY(),"D")<90),">=60",IF(AND(DATEDIF(A1,TODAY(),"D")>=90,DATEDIF(A1,TODAY(),"D")<180),">=90",IF(AND(DATEDIF(A1,TODAY(),"D")>=180,DATEDIF(A1,TODAY(),"D")<365),">=180",IF(DATEDIF(A1,TODAY(),"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+","")))))
 
M

Ms-Exl-Learner

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

Remember to Click Yes, if this post helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top