1 Reply Latest reply: May 15, 2014 10:00 AM by Data Kruncher RSS

    Date Conversion to MM/DD/YY

    TomS _

      I would like to be able to export a date field in a Monarch table file to Excel in MM/DD/YY format.  The report input format was YYMMDD.  I was able to convert that to MM/DD/YYYY in the table by using Options Input Select M/D/Y.  Is there a way to show the year as the 2 digit year YY instead of the 4 digit year YYYY?   Or do I have to use the calculauted field function?  I am using Monarch Version 8.

       

      Thanks.

      TomS

        • Date Conversion to MM/DD/YY
          Data Kruncher

          The way I can see that you can do this (using v9) is with a calculated field.

           

          I came up with this formula which forces two digit day, month, and year:

           

          [SIZE=2]right("0" + trim(str(month(mydate))),2) + "/" +/SIZE

          right("0" + trim(str(day(mydate))),2) + "/" +/SIZE

          right(trim(str(year(mydate))),2)[/SIZE][/code]

           

          Edit: This is a Character field type.

           

          2nd Edit: I missed the part about exporting the dates to Excel. Sorry. Why not just format the two digit year in Excel using cell formatting? You'll need to do that anyway, because when you export mm/dd/yy to Excel, Excel converts it to mm/dd/yyyy anyway, no matter how you finesse the format with Monarch.

           

          HTH,

          Kruncher