IBM Support

File from Excel shows date in five-digit numeric format

Troubleshooting


Problem

I have a Microsoft Excel data file which contains date values. These dates imported as numeric values into IBM SPSS Statistics. How can I convert these values to a proper date value for Statistics?

Resolving The Problem

If the Excel dates are not only being read into IBM SPSS Statistics as 5-digit integers but also they are being stored in Statistics as a string variable, then please see Technote 1489431.

Date values formatted as dates in Excel will typically import correctly into Statistics as dates. Microsoft Excel stores dates as sequential serial numbers of days since Jan 1, 1900 and may pass these numbers to Statistics when the file is accessed. We store dates as number of seconds since midnight (12:00am) Oct 14, 1582. To convert their dates to ours, use the following commands:

COMPUTE NEWDATE = DATE.MDY(1,1,1900) +( (excdate - 2) * 24 * 60 * 60).
FORMATS NEWDATE (DATE14).
EXECUTE.

where "excdate" is the Excel date variable. Basically, we add the number of seconds up to the beginning of Excel's calender to the Excel date (converting from days to seconds).

* * * *

If you need to convert many date variables from EXCEL
to Statistics, the following command will work:

DO REPEAT d= date1 to date5.
COMPUTE d= date.mdy(1,1,1900) + ((d-2)*24*60*60).
END REPEAT print.
DO REPEAT d=date1 to date5.
FORMATS d(date14).
END REPEAT print.

The print subcommand on the end repeat is optional. It will print out the process it is performing on each variable.

Note: The above solution assumes Excel is using the 1900 date system. From Excel help: "Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900." SPSS calculates this difference as an interval rather than start to end dates. Therefore, Statistics may appear 2 days off when comparing these values.

These values may only be one day coming from Excel for Macintosh. Excel for Mac uses the 1904 date system by default.

[{"Product":{"code":"SSLVMB","label":"IBM SPSS Statistics"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF033","label":"Windows"}],"Version":"Not Applicable","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

13636

Document Information

Modified date:
16 April 2020

UID

swg21476759