4 Replies Latest reply on Aug 5, 2015 1:30 PM by Eduardo Luiz

    DATEPARSE working for some values only

    Eduardo Luiz

      Hi,

       

      Can you help me to find out why the DATEPARSE function is working for some values only?

       

      For example:

       

      String Date Format:

      11JUN2015:11:41:59 (works fine)

      09APR2015:10:48:12 (not working)

       

      DATEPARSE formula:

      DATEPARSE("ddMMMyyyy:HH:mm:ss",[Issue Date])

       

      dateparseformula.png

      dateparse.jpg

      Thanks!

        • 1. Re: DATEPARSE working for some values only
          pooja.gandhi

          Hi Eduardo,

           

          Not sure why you see what you see, when I open your workbook and at the dateparse formula it seems to be working fine?

           

          Capture.PNG

           

          I haven't changed anything in your workbook! Try opening the workbook again and check if it is working!

          • 2. Re: DATEPARSE working for some values only
            Robby Burmeister

            I opened your workbook using Tableau Desktop version 9 and recreate the calculated field to parse the date and the April dates properly displayed.

            • 3. Re: DATEPARSE working for some values only
              Eduardo Luiz

              Look what I get after opening your workbook with recreated dateparse.

               

              dateparse robby.png

               

              I´m wondering if it can be related to character encoding/regional settings of my computer:


              Windows' regional settings:

              windows seetings.png

              data setting.png

               

              Any other ideas?

               

              Thank you Robby and Pooja for your help.

              • 4. Re: DATEPARSE working for some values only
                Eduardo Luiz

                Guys, as I was suspecting, this issue is related to the month translation:

                For ex: February (english) = FEB = FEV = Fevereiro (portuguese)

                 

                Additional details can be found here: Re: Dateparse returns null

                 

                For now, I had to improve the calculated field. This is not the best solution, but will do the work for today :

                 

                DATEPARSE("ddMMMyyyy",

                IF (MID([Insert Time],3,3) = "APR") then

                  STR(RIGHT([Insert Time],2) + "ABR" + MID([Insert Time],6,4))

                ELSEIF (MID([Insert Time],3,3) = "MAY") then

                  STR(RIGHT([Insert Time],2) + "MAI" + MID([Insert Time],6,4))

                elseif (MID([Insert Time],3,3) = "AUG") then

                  STR(RIGHT([Insert Time],2) + "AGO" + MID([Insert Time],6,4))

                elseif (MID([Insert Time],3,3) = "SEP") then

                  STR(RIGHT([Insert Time],2) + "SET" + MID([Insert Time],6,4))

                elseif (MID([Insert Time],3,3) = "OCT") then

                  STR(RIGHT([Insert Time],2) + "OUT" + MID([Insert Time],6,4))

                elseif (MID([Insert Time],3,3) = "DEC") then

                  STR(RIGHT([Insert Time],2) + "DEZ" + MID([Insert Time],6,4))

                elseif (MID([Insert Time],3,3) = "FEB") then

                  STR(RIGHT([Insert Time],2) + "FEV" + MID([Insert Time],6,4))

                else

                  STR(RIGHT([Insert Time],2) + MID([Insert Time],3,3) + MID([Insert Time],6,4))

                END

                 

                Thanks!!