Working with dates - now pulled out ALL MY HAIR

[[Ela Bonbevan]]

Many thanks to all who helped with my date problems.  I was able to use
some of the syntax to fix some of my dates and I am most grateful for the
help.

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.  The other comes like
this 1/20/1999 and displays like this 20-Jan-99.  Today when I did the
excel import it brought up the following 4 cells.  Where 38058 was
supposed to be 13 March 04 and 38205 was supposed to be 7 August 04.  I
have made numerous attempts to fix this by standardizing the formats in
Excel before I bring the sheet in but formatting cells appears to have no
effect.

08-JAN-04 38058            07.07.04  38205

Any help??

Diane

Diane

[Richard Ristow]

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 <[hidden email]>              .
*   Subject:      Excel dates, again (was, re: Date question...).
*   To: [hidden email]


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 <[hidden email]>
Subject: Excel dates, again (was, re: Date question...)
To:      [hidden email]