1 Reply Latest reply: Apr 13, 2018 9:37 AM by Chris Porthouse RSS

    Date Manipulation

    Todd Breckenridge

      I have a number of different date types in a text file that I am building a model for.  The ones that are formatted YYYYMMDD I have been fine with getting those to format the way I want.  The one I am having issues with is when it is formatted YYYYMM. 

        • Re: Date Manipulation
          Chris Porthouse

          You didn't mention which version of Monarch you are using, but this should work for most.  Bring in the date field with the YYYYMM as a character type.  You can then create a new formula field with the data type of "Date/Time".  You can then use some string concatenation and the character to date conversion function.  For example, if my character date field name is "date2", with the value of "201801" for January 2018, I can use this formula:

          ctod(date2+"01","y/m/d")

          which converts this to 01/01/2018.

           

          If you want to use the last day of the month, modify the formula to:

          End_Of_Current_Month(ctod(date2+"01","y/m/d"))