9 Replies Latest reply on Apr 6, 2018 8:52 AM by Mike Seim

    Date calc to integer is not correct

    Fábio Rocha

      Hello guys,

       

      I have a question with dates in Tableau.

       

      I have a "Day" field which is 01-01-2016, 02-01-2016, 03-01-2016, etc. For calculation purposes I need to convert it to integer so I use INT [Day]. The problem is it's returning the wrong value.

       

      In excel the day 01-01-2016 is converted to 42370 and in Tableau it's converted to 42368. Whyyyyy???

       

      Is there any setting or formula to use, so Tableau calcs 01-01-2016 to 42370 as it should?

       

      Thanks!

        • 1. Re: Date calc to integer is not correct
          Simon Runc

          Interesting...I hadn't noticed this before, but a quick experiment shows that Tableau starts it's date referencing from 2nd Jan 1900 (day 1)

           

           

          Whereas Excel starts from 1st Jan 1900

           

           

          so that accounts for 1, of the 2 unit difference...I'm afraid I'm not sure what the other one is. I'd do a few more tests (on different years), but looks like you can just minus 2 from the Tableau one, to get back to Excel date/int referencing.

           

          Just going to ping Jonathan Drummey...he seems to know most things!

          • 2. Re: Date calc to integer is not correct
            Jim Dehner

            Hi Fabio

            I had the same problem a couple of months ago

            There is a 2 day difference between the Tableau base date and the Excel base date - you just need to make the 2 day adjustment

             

            Let me know how that worked

            Jim

            • 3. Re: Date calc to integer is not correct
              Simon Runc

              hi Jim,

               

              Did you ever found out why?...just interested

               

              My best guess (from a position of no knowledge!)...the earth period isn't exactly 365.25 days, so every N years there needs to be an extra leap year (on top of the 4 yearly ones), which some systems recognise and some don't

              • 4. Re: Date calc to integer is not correct
                Jim Dehner

                Simon - no I did not find out why - It doesn't make a lot of sense - I just accepted the fact and made the necessary adjustments to my calculations

                 

                Jim

                • 5. Re: Date calc to integer is not correct
                  Jonathan Drummey

                  Hi Simon,

                   

                  I do know something about this, and I learned a little more putting this post together.

                   

                  Super-high-level-overview: Computers typically store dates as the number of something since a given date, that date is the epoch date and is date (or time) 0.

                   

                  - UNIX systems store dates as the number of seconds since 1 January 1970.

                   

                  - Excel stores dates as the number of days since 0 January 1900 (a date that doesn't exist), we can sort of think of it as 31 December 1899 however due to a bit of strangeness Excel doesn't actually acknowledge 31 December 1899.  So day 1 in Excel is 1 January 1900. In a second bit of strangeness Excel returns day 60 as 29 Feb 1900, a leap year that didn't exist. My understanding is that the Excel developers did that because the Lotus 1-2-3 developers had made a mistake that they didn't catch until the software was in the field so long that correcting it (in their estimation) would have created more problems than they thought it was worth to fix. So 40ish years later we're still dealing with that technical debt.

                   

                  Here's a screenshot of Excel 2016:

                   

                  Screen Shot 2017-01-20 at 9.09.43 AM.png

                   

                  - Tableau stores dates as the number of days since 1 January 1900 like some databases do, so that is day 0 and 2 January 1900 is day 1. Tableau also doesn't have the non-existent 1900 leap year.

                   

                  Therefore as we're counting up the days what happens is between 1 Jan 1900 and 28 Feb 1900 there is 1 day difference between what Excel and Tableau report, then day 60 in Excel is the non-existent 29 Feb 1900 while in Tableau it becomes 2 Mar 1900, then after that there are 2 days of difference. Here's an example, I brought the above Excel file into Tableau and created a Tableau Date field with the formula DATE([Row ID]) and we can see the 1 and 2 day offsets.

                   

                  Screen Shot 2017-01-20 at 9.36.03 AM.png

                   

                  [Aside - I'd always been under the impression that there was only a 2 day offset until I wrote this and did the math, there are posts I've written where I said Excel's epoch date was 30 December 1899, which is effectively accurate so long as we're considering dates from 1 March 1900 onwards.]

                   

                  The DATEDIFF() calc I used in the above viz is showing that Tableau is respecting two different epoch dates in the same calculation, therefore to be able to predict when we might need to add or subtract 2 days to get the right values here's what to keep in mind:

                   

                  Excel source using default connector (from v8.3 onwards):

                  - date field in Excel (including from calculations inside Excel) will use Excel's epoch date

                  - date field coming from calculations in Tableau will use Tableau's epoch date

                   

                  Excel source using legacy (Microsoft JET) connector (so this also applies to text files using the legacy connector as well as MS Access):

                  - date field in Excel (including from calculations inside Excel) will use Excel's epoch date

                  - record-level date fields computed on a live connection will use Excel's epoch date

                  - record-level fields date fields that are materialized in a data extract will use Excel's epoch date

                  - regular aggregates computed on a live connection most likely will use Excel's epoch date (I can imagine cases where they might use Tableau's, but it's been awhile)

                  - table calcs that generate a date will use Tableau's epoch date (I think)

                  - date fields that are computed post-extract will use Tableau's epoch date

                   

                  The legacy connector was kind of a nightmare when it came to dealing with dates from text & Excel files, the default connector introduced in v8.3 makes life a lot easier.

                   

                  Jonathan

                  5 of 5 people found this helpful
                  • 6. Re: Date calc to integer is not correct
                    Simon Runc

                    Thank you so much Jonanthan...and education as always. Incredible it comes from a historic error....classic! (mind you I bet it happens a lot...I heard that Responsive ToolTip and Global Replace in RegEx were similar...in that they knew they were wrong, but once out in the wild, fixing them would cause other issues for users)

                     

                    Also hadn't really considered the impact on DATEDIFF and Live/Jet connections...etc.

                     

                    ...fascinating stuff!

                    • 7. Re: Date calc to integer is not correct
                      Fábio Rocha

                      Awesome piece of information! That explains everything

                       

                      Thanks a lot!

                      • 8. Re: Date calc to integer is not correct
                        Fábio Rocha

                        Thanks, just added 2 to the date and that worked out just fine

                         

                        Thanks

                        • 9. Re: Date calc to integer is not correct
                          Mike Seim

                          First of all, thanks to all of you for posting this thorough explanation of what's going on.  I was dumbfounded when I found the inconsistencies pop up in my data.  This post saved the day for my team and I.

                           

                          I have added an idea to create a new function DateToMeasure() to handle for this so that folks wouldn't have to know about the glitch.  Of course, the idea needs votes to get traction.  Give it a gander...  https://community.tableau.com/ideas/8601