MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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