All Date Values That Are Set on the Year 1900 Always Add 1 day When Exported


Condition: 

All Date value reports that are in the year 1900 always add 1 day when exported. The format "mm/dd/yyyy" always adds up 1 day except for 12/31/1900 

 

Examples of this are: 

Text file 

1/1/1900 

1/31/1900 

12/31/1900 

1/1/1901 

  

Exported file 

1/2/1900 

2/1/1900 

12/31/1900 

1/1/1901 

 

Cause: 

By default, Excel workbooks use the 1900 date system. The first supported day is January 1, 1900. When you enter a date value, Excel converts that dates into a serial number that represents the number of elapsed days since January 1, 1900. 

 

In contrast, the first day supported in the 1904 system is January 1, 1904. When you enter a date, Excel converts it into a serial number that represents the number of elapsed days since January 1, 1904. This gets into the leap year issue that TechRepublic member Paul mentioned. 

The difference between the two systems, and consequently, their serial numbers is 1,462 days. 1900 serial numbers are always 1,462 days larger than the 1904 systems. 

  

Excerpt from: https://www.techrepublic.com/blog/microsoft-office/office-solution-clearing-up-that-wacky-date-problem-when-copying-sheets/ 

  

Remedy: 

  1. 1. Click the File tab and choose Options.

    1. In Excel 2007, click the Office button, and click Excel Options.

    2. In Excel 2003, choose Options from the Tools menu. 

    3.  

  1. 2. In the left pane, choose Advanced.

    1. In Excel 2003, click the Calculation tab. 

  1.  

  2. 3. In the When Calculating This Workbook section, check the Use 1904 Date option, to change this setting.

    1. In Excel 2003, click the 1904 Date System option. 

  1.  

  2. 4. Click OK.