Export table with date in year 1900 in Excel format, it adds 1 day to the date all by itself?!

[SteveCarter1]

I'm in Australia so using dd/mm/yyyy format. Fabric DC is Australia East Coast.  Web browser correctly (for me) shows date.

This seems to only affect dates with a year of 1900.

I can easily reproduce this with a blank PBI report, enter data, manually type in some dates dates starting of 01/01/1900 then add that field to a table visual.

This doesn't seem to matter, but for this particular test I also changed the default date format to '*14/03/2001 (Short Date)'

I created this test case using PBI Desktop because I'm having issue with other reports that have to use Desktop.

I was also able to reproduce this by creating this same data and report from inside the PBI service itself.

I Enter data, and add these dates:

In Power BI Service my table visual looks like this:

I then export that table with the default 'Data with current layout' option:

Then open in Excel.

I understsand years prior to 1900 are not handled so the # isn't a big deal, but why is 1 day added to all of my 1900 dates only? The 1901 dates are left alone?

So next test I exported again but this time used Summarized data with Excel live connection:

Again the 1899 date is no surprise, but at least now the 1900 dates match what is in my report.

Next test is exporting using Summarized data but changing format to Excel format:

Same wrong result as when using 'Data with current layout' option. What is Excel doing differently between the live connection and this offline version?

Lastly I tested the summarized data with CSV format:

Well I wasn't expecting the different format for the 1899 date, but at least the 1900 dates haven't been fiddled with:

Can anyone help with this mystery? It has me stumped...

[ibarrau]

Hi. I think all this it's because DAX supports dates starting in 1900. As the amazing SQLBI Guys explain "DAX stores a date as a decimal number, where the integer part is the number of days elapsed since December 31, 1899 and the decimal part is the fraction of the day

DAX represents with 1 the day of December 31, 1899. Why this? Because the original implementation of Lotus 1-2-3 had a bug and considered 1900 as a leap year that included February 29, 1900. Because 1900 is not a leap year, the dates before March 2, 1900 where misrepresented in Lotus 1-2-3."

You can read more about that here: https://www.sqlbi.com/blog/marco/2018/08/16/dates-differences-between-excel-and-dax/

I hope that helps,

[SteveCarter1]

Thanks for that, but it does not explain why the Extract data options of Excel with live connection versus Excel produce different results. Based on above logic I would expect a consistent result in Excel, and even saying that, from what you've found it confirms they do support dates from 1st Jan 1900 so why is Excel changing the date all by itself?

[RDiaz156395]

Hi @SteveCarter1,

Did you actually find a solution for this?

[SteveCarter1]

No I did not, but based on the very interesting history given by ibarrau, it would seem it maybe has something to do with an ancient quirk related to Lotus 1-2-3.

It might be that dates from 1st March 1900 are okay based on that same explanation.

Why the export works in some formats correctly and not others, I do not know.