Quote Originally Posted by Haseeb Avarakkan View Post
Yes Rick. I got the slowness in Excel 2007 too (I do not have 2003). I think it is because of 1/1/9999. When I changed the formula to,

=COUPNCD(A1,"1/1/"&YEAR(A1)+1,4,1)-1

and tested with 10000 cells, calculation was normal. Also, I didn't know it is a part of Analysis ToolPak in earlier versions.
Okay then, we are back to two function calls for each of our formulas. Which is more efficient... I don't know. My MONTH call and your YEAR call should be equal... and both involve a single concatenation and both involve a single math operation, so they should balance out as well... so it comes down to which of LOOKUP or COUPNCD is more efficient, the answer to which I don't know. I guess, until someone who knows how to test the effeciency of these two functions and reports back here, one cannot go too wrong simply using whichever they find more comfortable.