Date + months
Posted by Dave on January 28, 2002 2:45 PM
I would like to take todays date and add 4.8 months to it ...4.8 would be the number of months worth of stock.
The formula would let me know what day stock would be cleared out...Thank you in advance
Dave
Posted by Juan Pablo G. on January 28, 2002 2:52 PM
How's this ?
=EDATE(A1,4.8)
You must have installed the Analysis ToolPak AddIn.
Juan Pablo G.
Posted by Aladin Akyurek on January 28, 2002 3:01 PM
=EDATE("29-jan-2002",0.5) gives 29-jan-2002 [NT]
Posted by Juan Pablo G. on January 28, 2002 3:14 PM
Re: =EDATE("29-jan-2002",0.5) gives 29-jan-2002 [NT]
Yes, it does. Read Help on this:
<<Months is the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.
If months is not an integer, it is truncated. >>
So, back to the drawing board i guess...
Juan Pablo G.
Posted by Aladin Akyurek on January 28, 2002 3:19 PM
Maybe
=EDATE(A1,ROUND(D1,0))-(D1-INT(D1))*30
where A1 is =TODAY() and D1 4.8?
Aladin
========
Posted by Juan Pablo G. on January 28, 2002 3:28 PM
Re: Maybe
I don't like date discussions, but here we go.
I don't think that the 4 months are a problem here, right ?, but, with your formula I get (Using TODAY() in A1)
28/06/2002 using only =EDATE(A1,ROUND(D1,0))
This are 5 months...
i think it should be =EDATE(A1,TRUNC(D1))
Now, the other part, i agree, but I add it, you substract it...
My formula
=EDATE($A$1,TRUNC(D1))+(D1-TRUNC(D1))*30
returns 21/06/2002
your returns 04/06/2002
Juan Pablo G. =EDATE(A1,ROUND(D1,0))-(D1-INT(D1))*30
Posted by Aladin Akyurek on January 28, 2002 3:34 PM
I meant doing substraction of course. [NT]
This are 5 months... i think it should be =EDATE(A1,TRUNC(D1)) your returns 04/06/2002
Posted by Aladin Akyurek on January 28, 2002 3:52 PM
Hmm..
> I don't like date discussions
Neither do I. You've got me all confused. I should have said:
=EDATE(A5,ROUND(D1,1))+(D1-INT(D1))*30
INT(D1) or TRUNC(D1) instead of ROUND(D1,1) is OK too.
The idea still counts although it didn't come out right, no? :)
How about time discussions?
Aladin
======== : This are 5 months... : i think it should be =EDATE(A1,TRUNC(D1)) : your returns 04/06/2002
Posted by Juan Pablo G. on January 28, 2002 3:57 PM
Re: Hmm..
> INT(D1) or TRUNC(D1) instead of ROUND(D1,1) is OK too.
Well, don't know about this... what if i want TODAY + 4.95 months ?
=INT(4.95) = 4
=TRUNC(4.95) = 4
=ROUND(4.95,1) = 5
Then you'd be WAY off mark, when adding 0.95*30 !!
> How about time discussions?
A little better, at least days ALWAYS have 24 hours, right ??
Juan Pablo G.
-------------
Posted by Aladin Akyurek on January 28, 2002 4:07 PM
Re: Hmm..
Yep. ROUND version is obviously not OK. =TRUNC(4.95) = 4 =ROUND(4.95,1) = 5
Posted by IML on January 28, 2002 6:19 PM
Weenie way =today()+146
Often stock deals are based on a 360 day year. If that's the case, make it +144