Wrong date formatting when exporting the date 1/1/1900 or earlier to Excel;
Article Number: 000039011 | Last Modified: 2018/06/12
MS Excel for Windows is using the 1900 Date System which has the first day starting on Jan 1st 1900, so the formatting of earlier dates are seen as negative numbers and the cell displays pound (###) signs. As for the 1/1/1900 date, the numeric value being exported for this day is '2' and not '1'. This is due to a well-known Microsoft incorrect assumption of 1900 being a leap year. The 1900 year was a normal 365 days year (February had 28 days), but when the numeric value '60' + Date formatting is used in Excel, Feb 29th will be displayed.
In summary, MS Excel is using a date system by default (1900 Date System) with an unresolved leap year bug.
In order to display prior to 1/2/1900 dates correctly please review alternatives provided by Microsoft. More details under the links below:
Negative date and time value are displayed as pound signs (###) in Excel https://support.microsoft.com/en-us/help/182247/negative-date-and-time-value-are-displayed-as-pound-signs-in-excel
Differences between the 1900 and the 1904 date system in Excel https://support.microsoft.com/en-us/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel
Calendar for Year 1900 (United States) https://www.timeanddate.com/calendar/?year=1900
Collaborate with over 60,000 Qlik technologists and members around the world to get answers to your questions, and maximize success.