1 2 Previous Next 29 Replies Latest reply on Apr 19, 2017 6:21 AM by jorge.garcia.5 Go to original post
      • 15. Re: Formatting Date Time
        Stephen Lynch

        Thanks Rajeev. I had come across this article before posting my question. As per my original post, I've attempted several DATEPARSE commands with no luck

        • 16. Re: Formatting Date Time
          Stephen Lynch

          Hi Corey,

          The date format of DD/MM/YYYY is standard in Europe and I have used it successfully before.

           

          At this stage I need to accept (again) that despite the original date/time format being in a perfectly acceptable format, that Tableau wont read it correctly.

           

          This is not the first time I've had to edit the source file first before I can work in Tableau (without having to carry out table calculations). It is the very nature of analysis work that different data sources will have different date formats and Tableau should be able to handle it like a breeze. But it doesn't seem to. It could also be down to my intermediary Tableau skills but I just dont think I should have to create a calculated field to convert a normal date/time format.

           

           

           

          Stephen

          • 17. Re: Formatting Date Time
            corey levinson

            making a calculated field of str([date]) + ":00" and then doing a dateparse didn't work either?

            • 18. Re: Formatting Date Time
              Stephen Lynch

              Hi Corey,

              As I cant spend any more time on this, and have to actually do some analysis!, I have stripped out the date and time into separate columns in Excel and it works now in Tableau. I still dont understand why I should have to edit my source file when the time format is already an acceptable layout (from what I can see).

               

              If a Tableau Administrator could answer my first query, which is, why do they think the date/format presented in "08 Aug 2016 11:05 am", is not acceptable, it would be great to know so I can learn from this issue.

               

              Anyhoo, I shall struggle on. Thank you all for your help.

              Stephen

              • 19. Re: Formatting Date Time
                kettan

                I just connected to your DateFormatSample.csv file in Tableau 9.0 (Windows) without any issue whatsoever!

                 

                All 16 rows - with a date and time (the file has a lot of empty lines) - seems to be just fine.

                See screenshot at the bottom of this comment.

                 

                What are your { Windows > Region and Language > Formats } settings?

                 

                Mine are:

                 

                 

                In case your Windows (or Mac?) date format is different, and you don't want it changed, you can just set the locale in Tableau as (indirectly) shown in this gif animation:  Re: What is the correct schema.ini specification for this data?

                 

                2 of 2 people found this helpful
                • 20. Re: Formatting Date Time
                  Mark Fraser

                  Hi Stephen

                   

                  Appreciate you have circumvented the issue but...  I will be using dd/mm/yyyy

                   

                  Just wanted to touch on.. Dates in Excel

                  In Excel 'dates' are really sequential serial numbers (that is why you can easily + and - dates in Excel)

                  In Excel 01/01/1900 is Day 1, 02/01/1900 is day 2 and so on

                  We can see it with a simple example, in Excel today is really day 42,593

                  You can see this behavior in Cory's earlier answer...

                  The take away is - you can't always trust what you see in Excel

                   

                  Onto your CSV file, here are my steps -

                  Extracted your zip file to the desktop (DateFormatSample.csv)

                  Open Tableau Desktop (9.3.5)

                  Connect to Text File

                  Its interpreted as a date/time as expected...

                  and

                  I also downloaded the twb file, open, choose the CSV (from the previous step) and get the same as above?!

                  Re-attached the source file.

                   

                  Can you confirm if you're also seeing this behaviour?

                  And which version of Desktop are you using?

                   

                  Cheers

                  Mark

                  1 of 1 people found this helpful
                  • 21. Re: Formatting Date Time
                    Stephen Lynch

                    Hi Mark and Kettan,

                    Thank for you the detailed help. Yes, the workbook and CSV samples do work as expected. It's the unedited source file that doesn't work correctly for me.

                     

                    To test again, I started a new Tableau workbook and imported the CSV file again without even opening it in Excel (just in case Excel was doing something to it if I saved it). The CSV file is a report generated by Mailchimp on e-mail campaigns. I can repeat the error again with Tableau not picking up the date/time correctly.

                     

                    HOWEVER, I have figured out why the dateparse wasn't working and it was because I was using lowercase 'mmm' not uppercase 'MMM' for the month. I have now successfully converted the string date/time to the correct format

                     

                    It still doesn't explain why I cant get Tableau to read it correctly though!

                    • 22. Re: Formatting Date Time
                      Stephen Lynch

                      Thanks Kettan. I'm going to look at those items now and see how I get on. I'll let you know.

                      • 23. Re: Formatting Date Time
                        Stephen Lynch

                        HI Kettan,

                        Here are some of my settings. I didnt have any luck changing the parameters; the date/time is still read as a string.

                        schema.PNGRegionLanguage.PNG

                        • 24. Re: Formatting Date Time
                          kettan

                          Did you also  { step 1: change the field separator to Tab }  and  { step 2: thereafter back to Comma }  as recorded in  Re: What is the correct schema.ini specification for this data?

                           

                          In referred gif recording, original data source had Comma as field separator, which Tableau didn't recognize as so. Changing field separator to Tab (or other) and back to Comma helped Tableau recognize Comma as field separator! Strange, but true

                           

                          Does Tableau recognize your field separator?

                          If not, following above mentioned steps might help.

                           

                          If this helped, the issue was actually more about Tableau not recognizing comma immediately as field separator than recognizing a dateformat and therefore hope Tableau reads this comment and thus becomes aware of this not so nice user experience scenario.

                          • 25. Re: Formatting Date Time
                            Stephen Lynch

                            Hi Kettan,

                             

                            1) I did try the steps you suggested but it made no difference.

                             

                            2) The datetime format is as per the screenshot you mentioned. (The "08/08/2016 17.29" was how excel was reading it in. So it was confusing things)

                             

                            3) I've have a new (different) data set in TXT format with a date format of "2015-05-01 00:00:00.637000000", and thought "this will be interesting to see what Tableau does with it". Tableau pulled it straight in with no problems!

                             

                            4) Is there possibly something wrong with the way Mailchimp is creating the reports as clearly Tableau is working for me with other sources.

                            • 26. Re: Formatting Date Time
                              kettan

                              4) Is there possibly something wrong with the way Mailchimp is creating the reports as clearly Tableau is working for me with other sources.

                              Anything is possible, but don't know if something is wrong with the way Mailchimp creates csv files. It could also be:

                               

                              • a data issue with text having reserved "characters" for reading the csv file,
                                such as field separator, row separator, text qualifier (")
                              • an issue with Tableau
                              • corrupted file (such as incomplete download)
                              • something else

                               

                              My guess is that it is something else.

                               

                              Could you share a few rows (even a single should be enough) of your original CSV?

                               

                              It would of course be easiest to test it if we got it as it is, but if it contains sensitive info, you must of course change this to dummy info first.

                               

                              Dates are better kept as they are as to avoid misunderstandings in communication.

                               

                              And of course, test if changed file still has issues.

                               

                              Ps. My testing will be in Tableau 9.0 (or older), because I don't have license to newer versions.

                              • 27. Re: Formatting Date Time
                                jorge.garcia.5

                                Hello Stephen,

                                 

                                Did you find a solution for the formatting?

                                 

                                I have a date in format "Jan 20 2016 12:00AM" on a text file (pipe separated values) and can't get working correctly. I dont care about the hour because I got the real hour on another field, but the date is not working.

                                 

                                Already tried DATEPARSE("mmm dd yyyy HH:mm",[Sale Date])

                                 

                                Thanks.

                                • 28. Re: Formatting Date Time
                                  kettan

                                  This formula works for "Jan 20 2016 12:00AM" in Tableau 9.0:

                                   

                                  DATEPARSE( "MMM dd yyyy", [Sale Date] )

                                   

                                  MMM in upper case is used for 3 letter long month abbreviations.

                                   

                                  PS! If this didn't help, you should probably check if your computer's language is the same as the language of your data. You can read more about this here:  DATEPARSE foreign month names.  Although I haven't been a user of Tableau since version 9.0, I assume this is still an issue, because this idea is open:

                                   

                                  Make locale work for functions in a calculated field (17)

                                  • 29. Re: Formatting Date Time
                                    jorge.garcia.5

                                    Hello Kettan, alredy tried the same as you posted but is not working on 10.2.0

                                     

                                    I solved this thing with:

                                     

                                    1.- take every part of the date with LEFT and MID functions (one for month, another for the year and other for the day)

                                    2.- In the month i have to use a CASE to convert Jan to 01, Feb to 02, etc

                                    3.- With MAKEDATE created the new date (with the help of INT)

                                    4.- Finished with 5 objects, just for the date!

                                     

                                    Tableau is a nightmare!

                                     

                                    What if you create a dashbord and some users uses MM/DD/YY, other users use YYYY-MM-DD and other several combinations around the world!

                                     

                                    Thank you Kettan.

                                    1 2 Previous Next