Problem Note 41000: Date values might be incorrect when you read Microsoft Excel files with the XLS or XLSX engines
Date values might be incorrect when you use the XLS or XLSX engines to import Excel files. The dates are off by 60 years and one day. For example, the date 02/24/2003 is imported as 02/25/2063.
To circumvent the problem, use one of the following methods:
- In Excel, select the cells and format them as date values rather than as generic columns.
- If you cannot change the data, subtract 21,916 from the date value. Here is an example:
mynewdate=mydate-21916;
format mynewdate mmddyy10.;
Operating System and Release Information
SAS System | SAS/ACCESS Interface to PC Files | Microsoft Windows XP Professional | 9.2 TS2M0 | |
Microsoft Windows Server 2003 for x64 | 9.2 TS2M0 | |
Microsoft Windows Server 2008 for x64 | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M0 | |
Microsoft® Windows® for x64 | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M0 | |
Microsoft Windows XP 64-bit Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M0 | |
Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M0 | |
Windows Vista | 9.2 TS2M0 | |
Windows Vista for x64 | 9.2 TS2M0 | |
64-bit Enabled AIX | 9.2 TS2M0 | |
64-bit Enabled HP-UX | 9.2 TS2M0 | |
64-bit Enabled Solaris | 9.2 TS2M0 | |
HP-UX IPF | 9.2 TS2M0 | |
Linux | 9.2 TS2M0 | |
Linux for x64 | 9.2 TS2M0 | |
Solaris for x64 | 9.2 TS2M0 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
When you import Excel files with the XLS or XLSX engines, the date values might be off by 60 years and one day.
Type: | Problem Note |
Priority: | medium |
Date Modified: | 2010-10-12 14:33:22 |
Date Created: | 2010-09-22 15:44:21 |