
1. Re: Date calc to integer is not correct
Simon Runc Jan 20, 2017 4:51 AM (in response to Fábio Rocha)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 Jan 20, 2017 4:54 AM (in response to Fábio Rocha)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 Jan 20, 2017 4:58 AM (in response to Jim Dehner)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 Jan 20, 2017 5:13 AM (in response to Simon Runc)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 Jan 20, 2017 6:54 AM (in response to Simon Runc)Hi Simon,
I do know something about this, and I learned a little more putting this post together.
Superhighleveloverview: 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 123 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:
 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 nonexistent 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 nonexistent 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.
[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
 recordlevel date fields computed on a live connection will use Excel's epoch date
 recordlevel 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 postextract 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

Excel and Tableau epoch dates.twbx 343.8 KB


6. Re: Date calc to integer is not correct
Simon Runc Jan 20, 2017 7:21 AM (in response to Jonathan Drummey)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 Jan 23, 2017 1:12 AM (in response to Jonathan Drummey)Awesome piece of information! That explains everything
Thanks a lot!

8. Re: Date calc to integer is not correct
Fábio Rocha Jan 23, 2017 1:13 AM (in response to Jim Dehner)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 Apr 6, 2018 8:52 AM (in response to Fábio Rocha)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