2 Replies Latest reply on Sep 4, 2014 11:09 AM by slraymond

    Converting Date from dd-mmm-yyyy

    slraymond

      I was using this calculation to convert a date from dd-mm-yyyy:

       

      Let([

      adj =Substitute ( Self ; "-" ; ¶ ) ;

      month = GetValue ( adj ; 2 ) ;

      day = GetValue ( adj ; 1 ) ;

      year = GetValue ( adj ; 3 )

      ];

      Date ( month ; day ; year )

      )

       

       

      All was well, until the format of the spreadsheet from which I import data changed. Now it's dd-mmm-yyyy, as in "08-jun-2014."

       

      Now, my dates all are converted to something between 12/1/2013 and 12/31/2013. What is happening? I assume it has to do with the text month.

        • 1. Re: Converting Date from dd-mmm-yyyy
          erolst

          slraymond wrote:

          What is happening? I assume it has to do with the text month.

          That's a good guess …

           

          Try:

           

          Let ( [

            adj = Substitute ( Self ; "-" ; ¶ ) ;

            monthName = GetValue ( adj ; 2 )  ;

            ~day = GetValue ( adj ; 1 ) ;

            ~year = GetValue ( adj ; 3 ) ;

             monthList = "jan¶feb¶mar¶apr¶may¶jun¶jul¶aug¶sep¶oct¶nov¶dec" ;

             ~month = ValueCount ( Left ( monthList ; Position ( monthList ; monthName & ¶ ; 1 ; 1 ) ) )

            ] ;

            Date ( ~month ; ~day ; ~year )

          )

          • 2. Re: Converting Date from dd-mmm-yyyy
            slraymond

            Works great, thanks! I'm going to have to take a few minutes to study this; I'm a bit handicapped with calculations.