EXCEL DATES - error message - final solution

[thara vardhan-2]

Dear List Members

Once again I would like to offer my heartfelt  thanks to all members who
helped me solve the problem. It has been a tremendous experience learning
so much through all the possible options suggested.

For the benefit of all I am posting the problem and its solution.

Problem:

1. I have a variable pbirthdate in excel which is a date variable. However
there are also text  values 'unknown'  in 58 records. ( Sorry - usually I
do check but this time I missed it).The variable is a "date" but the cell
format is *14/03/2001.

2. When I tried to read the file in SPSS version 17.0 -  'pbirthdate'
05/06/1954 comes into SPSS as a string variable 19849.

3.Solution 1: Offered by Jon Peck

1.Off list we have determined that missing values in the date column are
being represented with text values in Excel.  Fixing that lets SPSS import
the date values in the expected way.

2. BTW, a useful trick for diagnosing odd behaviour with Excel imports is
to create a column of values in Excel like =TYPE(A1), say, if A is the
column that imports oddly.  This produces a column of type codes.  You can
then easily scan or tabulate this column to see if the cell type varies.

In general, it's better to fix the Excel sheet rather than patch things up
after importing, although both approaches can work.

Jon Peck

3.Creating a column 'Type' in excel gave me values as 1 for a date and 2
for a 'unknown'. There were 58 records with   a value of 2.

4.I Removed the 'unknowns' from the pbirthdate in the excel file.SPSS read
them as missing values.

Solution 2: Offered by Richard Ristow - to fix the problem in SPSS after
importing the excel file.

Try, then, using the NUMBER function in SPSS:

COMPUTE  SPSSDate = DATE.MDY(01,01,1900)
                    + TIME.DAYS(NUMBER(Pbirthdate,F5))
                    - TIME.DAYS(1).

Richard Ristow

This works perfectly but gives output as a numeric in spssdate column. The
unknowns are treated as missing values. Go to the variable and change the
variable type to date in SPSS. Then check the values with the original in
excel file. It matches.

Finally Albert-Jan the syntax suggested by you works fine - but the value
in excel file is 14/03/2001- does not have an asterisk - the cell format
is *14/03/2001.
Moreover it comes into SPSS as a string variable '19849'

data list free/funnydate (a12).
begin data
*14/03/2001
end data.
numeric spssdate (edate30).
compute spssdate = date.dmy(
number(substr(funnydate, 2, 2), n2),
number(substr(funnydate, 5, 2), n2),
number(substr(funnydate, 8, 4), n4)).
exe.

Cheers!!
Albert-Jan

thanks
regards
Thara Vardhan
Senior Statistician
Performance Improvement & Planning

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

The information contained in this email is intended for the named recipient(s)
only. It may contain private, confidential, copyright or legally privileged
information.  If you are not the intended recipient or you have received this
email by mistake, please reply to the author and delete this email immediately.
You must not copy, print, forward or distribute this email, nor place reliance
on its contents. This email and any attachment have been virus scanned. However,
you are requested to conduct a virus scan as well.  No liability is accepted
for any loss or damage resulting from a computer virus, or resulting from a delay
or defect in transmission of this email or any attached file. This email does not
constitute a representation by the NSW Police Force unless the author is legally
entitled to do so.

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