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,