* =======================================================================
*  File:    importing_excel_dates.SPS .
*  Date:    22-Nov-2007 .
*  Author:  Bruce Weaver, bweaverb@lakeheadu.ca .
*  Notes:   How to deal with problems that can arise when importing
	    date variables from Excel .
* ======================================================================= .

* Importing DATE variables from Excel often leads to trouble.
* I have sometimes ended up with a STRING variable with an
* assortment of date formats, as in the file created below.

DATA LIST LIST / datestr (a11).
BEGIN DATA.
30/11/03
30/06/04
28/02/04
30/04/04
30-04-04
31-05-04
30-06-04
29-02-04
38073
38136
37954
38073
END DATA.

list.
  
List
  
datestr

30/11/03
30/06/04
28/02/04
30/04/04
30-04-04
31-05-04
30-06-04
29-02-04
38073
38136
37954
38073


Number of cases read:  12    Number of cases listed:  12

  
* The cases with "/" or "-" are in the European date
* format EDATE8.  They can be converted to date
* variables using the NUMBER function with the
* EDATE8 function.

compute #slash = index(datestr,'/') GT 0 .
compute #dash = rindex(datestr,'-') GT 0.

if #slash or #dash date = number(datestr, EDATE8).

VARIABLE LABEL date "Date".
VARIABLE LEVEL date (SCALE).
FORMATS date (DATE11).
VARIABLE WIDTH date(11).
list.
  
List
  
datestr            date

30/11/03    30-NOV-2003
30/06/04    30-JUN-2004
28/02/04    28-FEB-2004
30/04/04    30-APR-2004
30-04-04    30-APR-2004
31-05-04    31-MAY-2004
30-06-04    30-JUN-2004
29-02-04    29-FEB-2004
38073                 .
38136                 .
37954                 .
38073                 .


Number of cases read:  12    Number of cases listed:  12

  
* If those cases had been in the American date format,
* I would have used format ADATE8 instead of EDATE8 in
* the NUMBER function when converting DATESTR to DATE.

* ------------------------------------------------------ .

* The last 4 cases in the file are from a different kettle of fish.
* Remember that these data came from Excel.

* Excel stores dates using either the 1900 or 1904 or system.
* The default is the 1900 system.

* In the 1900 system, 1-Jan-1900 is stored as a 1; 2-Jan-1900 as a 2, etc.
* The maximum numeric value is 295846, which corresponds to 31-Dec-9999.

* In SPSS, a date variable is stored as the number of seconds
* from 0:00:00 14-Oct-1582 to the date and time specified.
* If no time is specified in the value of a date variable,
* a time of 0:00:00 is assumed.

* In SPSS, 31-Dec-1899 is stored as 10010304000.
* In SPSS, 01-Jan-1900 is stored as 10010390400.
* In SPSS, 02-Jan-1900 is stored as 10010476800.

* 10010390400- 10010304000 = 86,400, the number of seconds in one day.
* 10010476800- 10010390400 = 86,400, the number of seconds in one day.

* So, to convert the number-strings in DATESTR to true dates, we must
* do the following:
*  [1] convert the number-strings to numbers
*  [2] convert the numbers from the Excel system to the SPSS system
*  [3] set variable DATEVAR equal to that numeric value.


do if missing(date).
-	compute excelnum = number(datestr,f11.0).
-	compute spssnum = excelnum * 86400 + 10010304000 .
-	compute date = spssnum.
end if.
exe.
format excelnum spssnum(f11.0).
list.
  
List

datestr            date    excelnum     spssnum

30/11/03    30-NOV-2003           .           .
30/06/04    30-JUN-2004           .           .
28/02/04    28-FEB-2004           .           .
30/04/04    30-APR-2004           .           .
30-04-04    30-APR-2004           .           .
31-05-04    31-MAY-2004           .           .
30-06-04    30-JUN-2004           .           .
29-02-04    29-FEB-2004           .           .
38073       28-MAR-2004       38073 13299811200
38136       30-MAY-2004       38136 13305254400
37954       30-NOV-2003       37954 13289529600
38073       28-MAR-2004       38073 13299811200


Number of cases read:  12    Number of cases listed:  12

  
* ======================================================================= .