4 Replies Latest reply: May 15, 2014 10:13 AM by OddJob _ RSS

    Convert MMM DD, YYYY date format

    ZacTechz _

      I have been unable to find this situation in the forums: I have a report filled with dates in the following format Mar 15, 2012.  I need to 2012-03-15 or YYYY-MM-DD. Thoughts?

       

       

      Thanks in advance

        • Convert MMM DD, YYYY date format
          Data Kruncher

          Well this is a bit nasty, isn't it?

           

          Because of the final presentation, Monarch won't be able to use a Date type field; it'll need to be Character. This might impact your decision based upon what it is that you need to do with your final Monarch data. Excel will treat YYYY-MM-DD as a date, other systems may not.

           

          Anyway, how do we get there? Not too straightforward, as you've no doubt determined already.

           

          We could do this however with one big formula-based calculated field, but it may be difficult to read back later if necessary. Instead we'll break this down into bite-sized pieces.

           

          First up, the month number. Create a MonthName Character field with:

          LSplit(RptDate;2;" ";1)[/CODE]Next we need to determine the month number. A Numeric Month field will do this:

          (Instr(MonthName;"janfebmaraprmayjunjulaugsepoctnovdec")+2)/3[/CODE]We could use a lookup calculated field instead of that formula. I like this one though.

           

          Now we build the Day Numeric field:

          Val(LSplit(Strip(RptDate;",");3;" ";2))[/CODE]And the final piece of the puzzle, the Year Character field:

          Trim(LSplit(Strip(RptDate;",");3;" ";3))[/CODE]Since the year will always be a four digit number, we may as well make it Character instead of Numeric. This makes the final calculated field just a little simpler.

           

          Speaking of which, the RealDate Character field gets this:

          Year"-"Str(Month,2,0,"0")"-"Str(Day,2,0,"0")[/CODE]All of this will get you output like this:

          Mar 15, 2012    Mar    3    15    2012    2012-03-15

          Jun 1, 2012     Jun    6     1    2012    2012-06-01

          Jan 15, 2012    Jan    1    15    2012    2012-01-15

          Feb 15, 2012    Feb    2    15    2012    2012-02-15

          Apr 15, 2012    Apr    4    15    2012    2012-04-15

          May 15, 2012    May    5    15    2012    2012-05-15

          Jul 15, 2012    Jul    7    15    2012    2012-07-15

          Aug 15, 2012    Aug    8    15    2012    2012-08-15

          Sep 15, 2012    Sep    9    15    2012    2012-09-15

          Oct 15, 2012    Oct   10    15    2012    2012-10-15

          Nov 15, 2012    Nov   11    15    2012    2012-11-15

          Dec 15, 2012    Dec   12    15    2012    2012-12-15

          /CODE

           

          How's that?

            • Convert MMM DD, YYYY date format
              ZacTechz _

              Greatly appreciate you taking the time to not only provide the solution but explaining it all.  I ended up smashing it into one big formula as I wanted the single field without hiding them all.

               

              (Trim(LSplit(Strip(Date;",");3;" ";3)))"-"Str(((Instr((LSplit(Date;2;" ";1)),"janfebmaraprmayjunjulaugsepoctnovdec")2)/3),2,0,"0")"-"+Str((Val(LSplit(Strip(Date;",");3;" ";2))),2,0,"0")

              /CODE

                • Convert MMM DD, YYYY date format
                  Data Kruncher

                  Perfect!

                   

                  The only thing that I might add to that would be to include a comment or two in the formula, just in case someone needs to review it later. Use the /* */ syntax to add those.

                    • Convert MMM DD, YYYY date format
                      OddJob _

                      An alternative approach might be as follows:

                       

                      Monarch should recognise Mar 15, 2012 as being a date, albeit in a character field.

                      The CtoD() function should happily convert the long date in the original character field to a short date e.g.

                      create a new short date field called newdate containing the formula -  CtoD(RptDate,'m/d/y') which should return 03/15/2012

                       

                      Dates can be a real pain to work with, especially when you're trying to take them apart and put them back together so, the next step is to convert the short date back to a text string, so that it's easier to work with,  using the DtoC() function. This can just be wrapped around the CtoD() function in the same calculated field (remembering to change the field type from date to character) e.g.

                      DtoC(CtoD(RptDate,'m/d/y'))

                       

                      This leaves you with the short date of 03/15/2012 as a text string that can then easily be chopped up and put back together in a new calculated field (finaldate) as follows:

                      Right(newdate,4)+ "-" + Left(newdate,2 )+ "-" +Substr(newdate,4,2)

                      This should return 2012-03-15

                       

                      You can then either hide the newdate field or, if you really want everything in one calculated field, copy the formula from the newdate field and paste it in place of 'newdate' in the finaldate formula.