At 08:57 AM 11/25/2008, Lombardo, Barbara wrote:
>Would someone please tell me how to get Excel dates into SPSS. It
>shows as a date in Excel, 05/06/1954 but comes into SPSS as 19849.
Excel date values are *normally* imported into SPSS as SPSS date
values; but sometimes the conversion fails, for reasons not worth diagnosing.
If an Excel date imports as a number in the low tens of thousands,
it's probably the underlying integer value of the Excel date: the
number of days since the beginning of 1900, subject to a couple of glitches.
That can be used to calculate date's value in SPSS date form. The
following s tested:
NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.
COMPUTE SPSSDate = DATE.MDY(01,01,1900)
+ TIME.DAYS(ExcelNum)
- TIME.DAYS(1).
LIST.
List
|-----------------------------|---------------------------|
|Output Created |25-NOV-2008 20:40:19 |
|-----------------------------|---------------------------|
ExcelNum SPSSDate
19,849 05/06/1954
Number of cases read: 1 Number of cases listed: 1
=============================
APPENDIX: Test data, and code
=============================
DATA LIST LIST / ExcelNum (F5).
BEGIN DATA
19849
END DATA.
FORMATS ExcelNum (Comma8).
NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.
COMPUTE SPSSDate = DATE.MDY(01,01,1900)
+ TIME.DAYS(ExcelNum)
- TIME.DAYS(1).
LIST.
=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD
Locked
