49717_en-US

liliconcom

Is there an equivalent of Excel's EOMonth function in JMP ?

All, 
    Trying to see is there an equivalent of EOMonth function of Excel in JMP ?  I tried to rewrite it as a User Defined Function. While it works with most cases , my code fails in some cases. Trying to see if there is an inbuilt function that JMP offers before I spend more time on this ? 

Source for Screenshot : ExcelJet 

Re: Is there an equivalent of Excel's EOMonth function in JMP ?

Here's another version that might be useful. This one has the advantage of being able to accept negative MonthToEval arguments and it also avoids needing to try multiple dates to see whether the date is valid or not.

My key change here was to push the month one extra month forward, and use Date MDY() to get the first day of that next month, then subtract a day away from the final answer.

EOMONTH = Function( {Date, MonthToEval},
 {Date, MonthToEval, EvalMonth, EvalYear, ReturnDate},
 EvalMonth = Month( Date ) + MonthToEval + 1;
 EvalYear = Year( Date );
 While( EvalMonth > 12, EvalMonth-=12; EvalYear++; );
 While( EvalMonth < 1, EvalMonth+=12; EvalYear--; );
 ReturnDate = Date MDY( EvalMonth, 1, EvalYear ) - In Days( 1 );
);

Re: Is there an equivalent of Excel's EOMonth function in JMP ?

Here is an alternative version of the same function.  It uses a simpler algorithm

EOMONTH = Function( {Date, MonthToEval},
 {Date, MonthToEval, EvalMonth, EvalYear, EvalDay, ReturnDate},
 EvalMonth = Month( Date );
 EvalYear = Year( Date );
 If( EvalMonth + MonthToEval > 12,
  EvalMonth = EvalMonth + MonthToEval - 12;
  EvalYear = EvalYear + 1;
 ,
  EvalMonth = EvalMonth + MonthToEval
 );
 EvalDay = 28;
 While( Is Missing( Date MDY( EvalMonth, EvalDay, EvalYear ) ) == 0,
  ReturnDate = Date MDY( EvalMonth, EvalDay, EvalYear );
  EvalDay = EvalDay + 1;
 );
 ReturnDate;
);

mydate = Date MDY( 2, 20, 2020 );
mymonth = 0;
Show( Format( eomonth( mydate, mymonth ), "mm/dd/yyyy" ) );

Re: Is there an equivalent of Excel's EOMonth function in JMP ?

Well, I guess I figured it out - but if anyone comes up with an easier / better / robust solution - please share - doesn't hurt . 

IsLeapYear = function({Input_Year},{Default Local},
      If(Is String(Input_Year),
       Input_Year = Num(Input_Year);
        );
      If( (Mod(Input_Year,400) == 0 | (Mod(Input_Year,4) == 0  & Mod(Input_Year,100)!= 0)),
       return(1);
       ,
       return(0);
        );
      );
EOMonth = function({Start_Date,Months},{Default Local},
      
      NewDate = Date Increment(Start_Date,"Month",Months,"start");
      
      NewMonth = Month(NewDate);
      NewDay = Day(NewDate);
      NewYear = Year(NewDate); 
      
      If(Contains({1,3,5,7,8,10,12},NewMonth),
       NewDay = 31; 
       ,
       If(Contains({4,6,9,11},NewMonth),
        NewDay = 30; 
        ,
        If(NewMonth == 2,
         If(IsLeapYear(NewYear), 
          NewDay = 29; 
          ,
          NewDay = 28;
           );
          );
         );
        ); 

      
      // Return Result 
      return(Date MDY(NewMonth,NewDay,NewYear));
      );

Start_Date = Date MDY(2,15,2015); 
Months = -15;
Show(As Date(Start_Date)); 
NewDate = EOMonth(Start_Date,Months); 
Show(As Date(NewDate));