2 Replies Latest reply: Jul 19, 2016 8:51 AM by Craig Kortlandt RSS

    Formula IF with Right function

    Craig Kortlandt

       

      =IF((RIGHT([Loan Type Code],1))="B",[Maturity Date],"")

       

      Can't get this to be accepted.  Any thoughts?  ---it works in excel :-(

       

      Loan Type Code is text

      Maturity Date is date

      (maturity date is a calculated field)

        • Re: Formula IF with Right function
          Olly Bond

          Hello Craig,

           

          A field in Monarch can be a numeric, character, memo or Date/Time field. It can't be more than one type at a time. Your formula suggests that if the LoanTypeCode ends in B, then the new field should be a Date, but that if LoanTypeCode doesn't end in B, then the new field should be an empty string, which only works as a character field.

           

          =IF((RIGHT([Loan Type Code];1))="B";dtoc([Maturity Date]);"")


          ... this uses the DTOC function to convert the date to a character field.


          =IF((RIGHT([Loan Type Code];1))="B";[Maturity Date];ctod(""))

           

          ... this uses CTOD to convert the character field to a date.

           

          Either of these should help, depending on what you want the new field type to be. CTOD and DTOC can take arguments about formats, and default date values are usually 1 Jan 1970, so you might want the dtoc approach to have a string to work with.

           

          Best wishes,

           

          Olly