EXCEL DATES - combining different formats in one syntax

[Eero Olli]

Dear list,

I am repeatedly importing excel files containing badly formatted dates,
which I always import as strings.  I have been using syntax that accepts
different formats.

The funnydate variable is a string containing dates in several formats.
I have identified some patterns, and use IF to import them all at once,
in order to avoid editing the excel files.

There are 3 different types of dates in my funnydates:
- no date (= zero length) this should be missing, thus no compute is
needed for the new variable.
- 39741       (excel date)
- 19.09.2006  (looks like proper date)

Thanks to Albert-Jan and Richad for providing very readable code. By
adding a few IF statements one can do them both. I use length to
distinguish between the different types of excelfiles (this is not
foolproof, and you need to know the patterns of dates present in your
file before you use this approach).

* the real dates.
IF LENGHT(RTRIM(funnydate)) > 6
  spssdate = date.dmy(
   number(substr(funnydate, 1, 2), n2),
   number(substr(funnydate, 4, 2), n2),
   number(substr(funnydate, 7, 4), n4)).
* excel date format.
IF LENGHT(RTRIM(funnydate)) = 5
  spssdate =  DATE.MDY(01,01,1900)
              + TIME.DAYS(NUMBER(funnydate,F5))
              - TIME.DAYS(1).
FORMATS spssdate(EDATE10).

Sincerely,
Eero Olli

________________________________________
Eero Olli
Advisor
the Equality and Anti-discrimination Ombud
[hidden email]                   [phone]
POB 8048 Dep,     N-0031 Oslo,      Norway

=====================
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 07:59 AM 12/3/2008, Eero Olli wrote:

>I am repeatedly importing excel files containing badly formatted dates,
[...]
>There are 3 different types of dates in my funnydates:
>- no date (= zero length) this should be missing, thus no compute is
>needed for the new variable.
>- 39741       (excel date)
>- 19.09.2006  (looks like proper date)
>
>Thanks to Albert-Jan and Richard for providing very readable code.
>By adding a few IF statements one can do them both.

Thanks for the kind words! and a good job on your code.

Code for the real-date form can be simplified slightly:

* the real dates.
IF LENGHT(RTRIM(funnydate)) > 6
   spssdate = NUMBER(funnydate,EDATE12).
* excel date format.
IF LENGHT(RTRIM(funnydate)) = 5
   spssdate =  DATE.MDY(01,01,1900)
               + TIME.DAYS(NUMBER(funnydate,F5))
               - TIME.DAYS(1).
FORMATS spssdate(EDATE10).

LIST.
List

Notes
|-----------------------------|---------------------------|
|Output Created               |04-DEC-2008 17:40:30       |
|-----------------------------|---------------------------|
funnydate      spssdate

39741        21.10.2008
19.09.2006   19.09.2006

Number of cases read:  2    Number of cases listed:  2
=============================
APPENDIX: Test data, and code
=============================
DATA LIST LIST /funnydate (A12).
BEGIN DATA
39741       (excel date)
19.09.2006  (looks like proper date)
END DATA.
LIST.

.  /**/ LIST /*-*/.

* the real dates.
IF LENGHT(RTRIM(funnydate)) > 6
   spssdate = NUMBER(funnydate,EDATE12).
* excel date format.
IF LENGHT(RTRIM(funnydate)) = 5
   spssdate =  DATE.MDY(01,01,1900)
               + TIME.DAYS(NUMBER(funnydate,F5))
               - TIME.DAYS(1).
FORMATS spssdate(EDATE10).

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

[Dennis Deck]

Note that LENGHT should be LENGTH

Dennis Deck
RMC Research

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