Reading an excel file in SPSS

[Guest]

I have an excel file that has a variable called date of birth (DOB) that is in the following form Jun-58 to represent June 1958. The file has 11,000 cases which creates a myriad of DOB options. I have imported the data file into SPSS, a statistical package for the social sciences. SPSS does not have a data format option to recognize the DOB format produced in the excel file since it is alphnumeric with a dash. Is there an "easy" way to recompute the DOB into a numeric variable in excel, given the format that it is currently in?

 

[Tom Ogilvy]

Insert a column next to the DOB column

assume the first cell with DOB is F2, in G2 (the new column) put in the
formula

=DateValue("1-" & trim(F2))
then drag fill down the column (or select G2 and double click on the little
black square on the lower right corner of the highlight.

Now select column G and do Edit=>Copy, then immediately Edit=>PasteSpecial
and select Values

format the column with a date format. If all the cells resolve OK, then you
can delete column F.

--
Regards,
Tom Ogilvy

I have an excel file that has a variable called date of birth (DOB) that

is in the following form Jun-58 to represent June 1958. The file has 11,000
cases which creates a myriad of DOB options. I have imported the data file
into SPSS, a statistical package for the social sciences. SPSS does not
have a data format option to recognize the DOB format produced in the excel
file since it is alphnumeric with a dash. Is there an "easy" way to
recompute the DOB into a numeric variable in excel, given the format that it
is currently in?

 

[Guest]

Use the Format function:

Format( MyDate,"mm/yyyy") will return 06/1958
if MyDate is a string use Format( CvDate(MyDate),"mm/yyyy")
----- Gary wrote: ----

I have an excel file that has a variable called date of birth (DOB) that is in the following form Jun-58 to represent June 1958. The file has 11,000 cases which creates a myriad of DOB options. I have imported the data file into SPSS, a statistical package for the social sciences. SPSS does not have a data format option to recognize the DOB format produced in the excel file since it is alphnumeric with a dash. Is there an "easy" way to recompute the DOB into a numeric variable in excel, given the format that it is currently in?

 

[Tom Ogilvy]

There is no CvDate function. The conversion function is CDate

Also, one reason not to use your approach is inconsistent results:

mydate = "Jun-15"
? Format( MyDate,"mm/yyyy")
06/2004

If the year can be interpreted as a day, it will be. This may not be a
problem for this particular data file - but then again, it may be.

--
Regards,
Tom Ogilvy

Use the Format function:

Format( MyDate,"mm/yyyy") will return 06/1958
if MyDate is a string use Format( CvDate(MyDate),"mm/yyyy")
----- Gary wrote: -----

I have an excel file that has a variable called date of birth (DOB)

that is in the following form Jun-58 to represent June 1958. The file has
11,000 cases which creates a myriad of DOB options. I have imported the
data file into SPSS, a statistical package for the social sciences. SPSS
does not have a data format option to recognize the DOB format produced in
the excel file since it is alphnumeric with a dash. Is there an "easy" way
to recompute the DOB into a numeric variable in excel, given the format that
it is currently in?

 

[Tom Ogilvy]

There is no CvDate function. The conversion function is CDate
I stand corrected on the CvDate function although it is only offered for
compatibility with older verisions of visual basic.

But it gives the same bad results:

? cvDate("Jun-15")
6/15/04