Need a Formula using Dates
Posted by Stefanie on July 28, 2000 9:01 AM
I need a formula to help me calculate the number of months vested. If the grant date is 01/01/00 and the current date is 03/01/2001 - then the number of months vested should read - 14. With the formula I have, I get 13. The current date can be any date in the month. What I want is for it to display 14 from 03/01/2001-03/30/20001 - as soon as 04/01/2001 comes, it should change to 15 and no sooner than that. It isn't working that way.
Here is my formula =ROUNDDOWN(($K$1-C5)/365*12,0). I have tried many variations of this and nothing seems to work. $k$1 = current date and c5=01/01/2000.
Any help would be appreciated! Thanks
Posted by Stefanie on August 01, 0100 10:25 AM
Thanks for the responses. It worked great!
Posted by Tim Francis-Wright on July 28, 0100 2:59 PM
I have a formula that is pretty simple-minded
but seems to work:
=12*(YEAR($K$1)-YEAR(C5))+MONTH($K$1)-MONTH(C5)-IF(DAY($K$1)<DAY(C5),-1,0)
HTH
Posted by Ada on July 28, 0100 5:29 PM
There is a small error in the last part of the formula. One of the last two minus signs needs to be changed to plus :-
=12*(YEAR($K$1)-YEAR(C5))+MONTH($K$1)-MONTH(C5)-IF(DAY($K$1)<DAY(C5),1,0)
The DATEDIF function can also be used to produce the number of months :-
=DATEDIF(C5,$K$1,"m")
Ada
