I have a complicated financial model (xlsm 2010) that is recently giving me fits.
I am using date(yyyy+x,mm+1,1) to get the first of the next month. Here's the actual formula:
=DATE(YEAR(LANDACQ_DATE)+BJ8,MONTH(LANDACQ_DATE)+1,1)-1 where LANDACQ_DATE is a input date and BJ8 is an integer from 1 to 12.
When it hits month 13, it should roll to January. Very familiar with it and used it in this same model.
A co-worker sent me a model with an error in it I traced to a row of date functions. The formula Date(yyyy,mm+1,dd) is returning !NUM#. I try breaking the formula into steps, A4 to calc the year, A5 for the month, and A6 for the day. Guess what? Date(A4,A5,A6) returns !NUM#. To check my syntax, I type in =date(2017,13,1) and I get !NUM#.
The really weird part, when I type that formula on ANY OTHER sheet in that workbook, it works.
Bookmarks