Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Termination within number of days
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,389
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,520
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,520
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 506
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,651
Default 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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 506
Default 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



.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with Excel formula for a termination report Courtney Rae Excel Discussion (Misc queries) 1 February 20th 09 10:14 PM
how do i convert a number of days to years, months & days? SafetyLen Excel Discussion (Misc queries) 1 August 23rd 07 01:34 AM
Number of days between days Joe Excel Discussion (Misc queries) 4 August 14th 07 12:32 PM
how do I convert a number to number of years, months and days because Excel Worksheet Functions 2 October 12th 05 06:15 PM
A number of days into weeks and days Robert Christie Excel Worksheet Functions 4 August 31st 05 03:23 AM


All times are GMT +1. The time now is 08:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement