If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula Creation
I need to create a formula that will provide a result that calculates a date
three years out and another date one year out with the result being the greater of the two? 12/31/2007 three years out 12/31/2010 or 1/9/2008 one year out 1/9/2009 but I need the result to give me the answer as displayed above. -- ASL |
#2
|
|||
|
|||
Formula Creation
Use
=MAX(Formula1,Formula2) to get 3 years will depend on how you want certain dates to display, one way =DATE(YEAR(A1)+3,MONTH(A1),DAY(A1)) where A1 holds the date, do the same for 1 year by changing +3 to +1 -- Regards, Peo Sjoblom "aleflore" wrote in message ... I need to create a formula that will provide a result that calculates a date three years out and another date one year out with the result being the greater of the two? 12/31/2007 three years out 12/31/2010 or 1/9/2008 one year out 1/9/2009 but I need the result to give me the answer as displayed above. -- ASL |
#3
|
|||
|
|||
Formula Creation
Peo,
Is aleflore worrying about the date format? In which case he should highlight the desired cells (or go to the single cell), invoke Format Custom "m/d/yyyy". EDATE function makes the adding years a cinch. It works with either a serial number or date, & returns a serial number, which can be a previously date-formatted cell. I hope I didn't misunderstand the question. aj "Peo Sjoblom" wrote: Use =MAX(Formula1,Formula2) to get 3 years will depend on how you want certain dates to display, one way =DATE(YEAR(A1)+3,MONTH(A1),DAY(A1)) where A1 holds the date, do the same for 1 year by changing +3 to +1 -- Regards, Peo Sjoblom "aleflore" wrote in message ... I need to create a formula that will provide a result that calculates a date three years out and another date one year out with the result being the greater of the two? 12/31/2007 three years out 12/31/2010 or 1/9/2008 one year out 1/9/2009 but I need the result to give me the answer as displayed above. -- ASL |
#4
|
|||
|
|||
Formula Creation
Wrong word, I didn't mean the formatting, I meant that depending on the
start date like for instance 01/31/2007, if we add one month, should it be 02/28/08 or 03/03/2007? EDATE does the former and the formula I posted the latter. Also note that EDATE might not be installed if the OP is using a company PC since it is part of the Analysis ToolPak. There is a way of getting the same result as EDATE using regular function =MIN(DATE(YEAR(A1),MONTH(A1)+{1,2},DAY(A1)*{1,0})) -- Regards, Peo Sjoblom "aj scott" wrote in message ... Peo, Is aleflore worrying about the date format? In which case he should highlight the desired cells (or go to the single cell), invoke Format Custom "m/d/yyyy". EDATE function makes the adding years a cinch. It works with either a serial number or date, & returns a serial number, which can be a previously date-formatted cell. I hope I didn't misunderstand the question. aj "Peo Sjoblom" wrote: Use =MAX(Formula1,Formula2) to get 3 years will depend on how you want certain dates to display, one way =DATE(YEAR(A1)+3,MONTH(A1),DAY(A1)) where A1 holds the date, do the same for 1 year by changing +3 to +1 -- Regards, Peo Sjoblom "aleflore" wrote in message ... I need to create a formula that will provide a result that calculates a date three years out and another date one year out with the result being the greater of the two? 12/31/2007 three years out 12/31/2010 or 1/9/2008 one year out 1/9/2009 but I need the result to give me the answer as displayed above. -- ASL |
Thread Tools | |
Display Modes | |
|
|