date problem

[Wroblewski, Matthew]

Hello - I'm importing data into SPSS from an Excel file which has two
date variables. Both date variables are formatted the same in Excel (as
"date", not "general" or "number") but only one of the variables imports
correctly into SPSS as a date; the other imports as a string variable
and the numerals aren't even the same as the numerals in the original
date field. Any thoughts on why this happens or how to fix it? Thanks.

 

Matt

 





Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**

====================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

[Richard Ristow]

At 09:02 AM 7/25/2008, Wroblewski, Matthew wrote:

>... the other [date variable] imports [from Excel] as a string
>variable and the numerals aren't even the same as the numerals in
>the original date field. Any thoughts on why this happens or how to fix it?

It usually happens because some value early in the column is a
string; SPSS then decides the variable is a string.

What values are you getting? Integers in the 30,000s are usually the
numerical form of Excel dates.

=====================
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

[Richard Ristow]

>>... a [date variable] imports [from Excel] as a string variable and
>>the numerals aren't even the same as the numerals in the date.
>
>Integers in the 30,000s are usually the numerical form of Excel dates.

At 12:11 PM 8/4/2008, Wroblewski, Matthew wrote, off-list:

>The values are mainly in the 10,000 - 20,000 range (graph below).
>Does that mean anything to you?

On the graph (which can't be sent to the list, of course), there's a
large bulge of values between about 6,000 and 10,000, and a smaller
bulge between about 42,000 and 48,000, with an abrupt cut-off in the
48,000s. If those are the numerical form of Excel dates, those
correspond to 1916-1927, and then 2014-2031:
|-----------------------------|---------------------------|
|Output Created               |04-AUG-2008 22:38:11       |
|-----------------------------|---------------------------|
  ExcelNum    SPSSDate

     6,000 04-JUN-1916
    10,000 18-MAY-1927
    42,000 27-DEC-2014
    48,000 01-JUN-2031

Number of cases read:  4    Number of cases listed:  4

Does this make any sense, in your problem? What should the date range
for this variable be?
==============
APPENDIX: Code
==============
DATA LIST FREE / ExcelNum.
BEGIN DATA
6000 10000 42000 48000
END DATA.

FORMATS ExcelNum (COMMA9).
NUMERIC SPSSDate (DATE11).

COMPUTE   SPSSDate = DATE.MDY(01,01,1900)
                    + TIME.DAYS(ExcelNum)
                    - TIME.DAYS(2).

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