4 Replies Latest reply: Aug 7, 2015 2:25 AM by ted soesanto RSS

    Ask Format Date.

      Dear All,

       

      I create qvw doc that load data from excel file. but some data is different format in one field (format date).

      How to equalize the format date from this excel file without edit formate date in the excel file.

       

      DATE.png

       

      hope someone help me.

       

      Regards,

      Ted

        • Re: Ask Format Date.
          Prashant Sangle

          Hi,

           

          Use Alt()

           

          Syntax with description from Help Menu

          alt(case1[ , case2 , case3 , ...] , else)

           

          The alt function returns the first of the parameters
          that has a valid number representation. If no such match is found, the last
          parameter will be returned. Any number of parameters can be used.

           

          Example:

           

          alt( date#( dat , 'YYYY/MM/DD' ),

           

          date#( dat , 'MM/DD/YYYY' ),

           

          date#( dat , 'MM/DD/YY' ),

           

          'No valid date' )

           

          Will test if the field date contains a date according to any of the three specified date formats.

          If so, it will return the original string and a valid number representation of a date.

          If no match is found, the text 'No valid date' will be returned (without any valid number representation).

           

          Regards

          • Re: Ask Format Date.
            Sunny Talwar

            This seems to work:

             

            SET DateFormat='D/M/YYYY';

             

            Table:

            LOAD [ENTRANCE DATE],

                [EXIT DATE],

                company

            FROM

            ask.xlsx

            (ooxml, embedded labels, table is Sheet1);

            • Re: Ask Format Date.
              sujeet singh

              You can use two ways to do this.

              One is just format it to date and see the different values.

              The next way is use conditional conversion based on the deliminter ., that means when it is '-' then one format else the other format.

              • Re: Ask Format Date.

                Dear all,

                 

                thank you for your help,

                 

                regards,

                Ted.