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?

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



"Gary" wrote in message
...
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?

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?

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


"chris" wrote in message
...
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?

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


--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
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


"chris" wrote in message
...
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?