LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (July 2006)Back to main SPSSX-L pageJoin or leave SPSSX-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
=========================================================================
Date:         Fri, 14 Jul 2006 02:29:17 -0400
Reply-To:     Richard Ristow <wrristow@mindspring.com>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         Richard Ristow <wrristow@mindspring.com>
Subject:      Re: Working with dates - now pulled out ALL MY HAIR
Comments: To: "[Ela Bonbevan]" <bonbevan@AOL.COM>
In-Reply-To:  <200607131713.k6DEx8Wi003646@mailgw.cc.uga.edu>
Content-Type: text/plain; charset=us-ascii; format=flowed;
              x-avg-checked=avg-ok-647E6F11

At 01:13 PM 7/13/2006, [Ela Bonbevan] wrote:

>I must confess that I don't find the date import from Excel to SPSS >very straightforward. The data in my excel database comes from two >sources and the date formats are all over the place. > >One set comes as 19950816 and this is the easiest.

That is, then, not an Excel date value, but an Excel integer whose digits give a date value. It was imported into SPSS as an integer, and you've had the correct advice how to convert it to an SPSS date.

>The other comes like this 1/20/1999 and displays like this 20-Jan-99.

That is, I think, an Excel date.

>Today when I did the excel import it brought up the following 4 cells.

Were those all in the same column, or in different columns?

>Where 38058 was supposed to be 13 March 04 and 38205 was supposed to >be 7 August 04.

It's not clear what's happening, although importing from an Excel spreadsheet when not all cells in a column haves the same format, can raise all kinds of Cain.

Excel dates are represented internally as integers, with dates near the present in the mid to high 30,000s. Where SPSS recognizes these as dates by the formatting of the cell, it will convert them to SPSS dates. If it doesn't recognize them, and imports the integer representation instead, you can convert to SPSS dates with the following formula(*), which is valid for 1 March 1900 and later (don't ask):

COMPUTE DATE_CVT = DATE.MDY(01,01,1900) + TIME.DAYS(DATE_IN) - TIME.DAYS(1).

(Earlier investigation, and understanding of what Excel does, determined that the last term there should be "TIME.DAYS(2)", but the formula above works for your dates - and for some others, encountered earlier.) This is SPSS draft output:

NUMERIC DATE_CVT (DATE11).

* In the following, the last term should be "TIME.DAYS(2)" . * per Jon Pack, and subsequent testing; but the formula works . * as given, for the dates in the posting responeded to. . * For Jon Peck's comments and subsequent tests, see posting . * Date: Thu, 20 Oct 2005 13:05:52 -0400 . * From: Richard Ristow <wrristow@mindspring.com> . * Subject: Excel dates, again (was, re: Date question...). * To: SPSSX-L@LISTSERV.UGA.EDU

NUMERIC DATE_CVT (DATE11). COMPUTE DATE_CVT = DATE.MDY(01,01,1900) + TIME.DAYS(DATE_IN) - TIME.DAYS(1). LIST.

List |-------------------------|------------------------| |Output Created |14-JUL-2006 02:26:11 | |-------------------------|------------------------| DATE_IN DATE_CVT

38058 13-MAR-2004 38205 07-AUG-2004

Number of cases read: 2 Number of cases listed: 2

........................................ (*)Date: Thu, 20 Oct 2005 13:05:52 -0400 From: Richard Ristow <wrristow@mindspring.com> Subject: Excel dates, again (was, re: Date question...)


Back to: Top of message | Previous page | Main SPSSX-L page