SPSSX Discussion

Convert string variable to date

Classic

List

Threaded

5 messages Options

No Idea

Convert string variable to date

I have a a date variable entered as string with two formats i.e. 12/01/2009 and 38361. How can I convert these to date variable with the format ddmmyyyy and width 20 in SPSS v.20? Requesting your help on this.

David Marso

Re: Convert string variable to date

Administrator

Presuming you got 38361 from Excel or some equivalent abomination (I hope 38361 is not supposed to be 12/01/2009 and you are just making up these numbers because they are not in synch).
See http://www.cpearson.com/excel/datetime.htm for info on Excel date and time.
RTFM re SPSS dates!
--
data list free / x1 (A5) x2 (a10).
begin data
38361 12/01/2009
end data.

COMPUTE datex1=(NUMBER(x1,F5))*86400 +date.mdy(1,1,1900).
COMPUTE datex2=NUMBER(x2,ADATE).
format datex1 datex2(adate).
list.

X1 X2 DATEX1 DATEX2

38361 12/01/2009 01/11/2005 12/01/2009

Number of cases read: 1 Number of cases listed: 1

No Idea wrote

I have a a date variable entered as string with two formats i.e. 12/01/2009 and 38361. How can I convert these to date variable with the format ddmmyyyy and width 20 in SPSS v.20? Requesting your help on this.

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

Bruce Weaver

Re: Convert string variable to date

Administrator

Yes, it does look like something Excel coughed up. I ran into this same problem a few years ago, and added a few links to my SPSS page after I figured it out.

http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt
http://www.angelfire.com/wv/bwhomedir/spss/excel_dates_output.txt
http://www.angelfire.com/wv/bwhomedir/spss/Dates_in_Excel_and_QuattroPro.pdf
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1005&L=spssx-l&F=&S=&P=27896

HTH.

David Marso wrote

Presuming you got 38361 from Excel or some equivalent abomination (I hope 38361 is not supposed to be 12/01/2009 and you are just making up these numbers because they are not in synch).
See http://www.cpearson.com/excel/datetime.htm for info on Excel date and time.
RTFM re SPSS dates!
--
data list free / x1 (A5) x2 (a10).
begin data
38361 12/01/2009
end data.

COMPUTE datex1=(NUMBER(x1,F5))*86400 +date.mdy(1,1,1900).
COMPUTE datex2=NUMBER(x2,ADATE).
format datex1 datex2(adate).
list.

X1 X2 DATEX1 DATEX2

38361 12/01/2009 01/11/2005 12/01/2009

Number of cases read: 1 Number of cases listed: 1

No Idea wrote

I have a a date variable entered as string with two formats i.e. 12/01/2009 and 38361. How can I convert these to date variable with the format ddmmyyyy and width 20 in SPSS v.20? Requesting your help on this.

--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

David Marso

Re: Convert string variable to date

Administrator

Thanks Bruce.
Looks like my code might be off by 1 day (or a few years).
David

Bruce Weaver wrote

Yes, it does look like something Excel coughed up. I ran into this same problem a few years ago, and added a few links to my SPSS page after I figured it out.

http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt
http://www.angelfire.com/wv/bwhomedir/spss/excel_dates_output.txt
http://www.angelfire.com/wv/bwhomedir/spss/Dates_in_Excel_and_QuattroPro.pdf
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1005&L=spssx-l&F=&S=&P=27896

HTH.

David Marso wrote

Presuming you got 38361 from Excel or some equivalent abomination (I hope 38361 is not supposed to be 12/01/2009 and you are just making up these numbers because they are not in synch).
See http://www.cpearson.com/excel/datetime.htm for info on Excel date and time.
RTFM re SPSS dates!
--
data list free / x1 (A5) x2 (a10).
begin data
38361 12/01/2009
end data.

COMPUTE datex1=(NUMBER(x1,F5))*86400 +date.mdy(1,1,1900).
COMPUTE datex2=NUMBER(x2,ADATE).
format datex1 datex2(adate).
list.

X1 X2 DATEX1 DATEX2

38361 12/01/2009 01/11/2005 12/01/2009

Number of cases read: 1 Number of cases listed: 1

No Idea wrote

I have a a date variable entered as string with two formats i.e. 12/01/2009 and 38361. How can I convert these to date variable with the format ddmmyyyy and width 20 in SPSS v.20? Requesting your help on this.

David Marso

Re: Convert string variable to date

Administrator

Hopefully "no idea" the OP -might reconsider that handle-?
has some idea as to whether the Excel date is 1900 or 1904 based?

data list free / x1 (A5) x2 (a10).
begin data
38361 12/01/2009
end data.

compute datex1=(NUMBER(x1,F5))*86400 +date.mdy(12,31,1899).
compute datex2=(NUMBER(x1,F5))*86400 +date.mdy(1,1,1904).
COMPUTE datex3=NUMBER(x2,ADATE).
format datex1 datex2 datex3(adate).
list.

X1 X2 DATEX1 DATEX2 DATEX3

38361 12/01/2009 01/10/2005 01/10/2009 12/01/2009
----
From the Excel Hellp
"How Microsoft Excel stores dates and times
Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and, therefore, can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900. To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box.

The following table shows the first date and the last date for each date system and the serial value associated with each date.

Date system First date Last date
1900 January 1, 1900 (serial value 1) December 31, 9999 (serial value 2958465)
1904 January 2, 1904 (serial value 1) December 31, 9999 (serial value 2957003)
--

Number of cases read: 1 Number of cases listed: 1

David Marso wrote

Thanks Bruce.
Looks like my code might be off by 1 day (or a few years).
David

Bruce Weaver wrote

Yes, it does look like something Excel coughed up. I ran into this same problem a few years ago, and added a few links to my SPSS page after I figured it out.

http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt
http://www.angelfire.com/wv/bwhomedir/spss/excel_dates_output.txt
http://www.angelfire.com/wv/bwhomedir/spss/Dates_in_Excel_and_QuattroPro.pdf
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1005&L=spssx-l&F=&S=&P=27896

HTH.

David Marso wrote

Presuming you got 38361 from Excel or some equivalent abomination (I hope 38361 is not supposed to be 12/01/2009 and you are just making up these numbers because they are not in synch).
See http://www.cpearson.com/excel/datetime.htm for info on Excel date and time.
RTFM re SPSS dates!
--
data list free / x1 (A5) x2 (a10).
begin data
38361 12/01/2009
end data.

COMPUTE datex1=(NUMBER(x1,F5))*86400 +date.mdy(1,1,1900).
COMPUTE datex2=NUMBER(x2,ADATE).
format datex1 datex2(adate).
list.

X1 X2 DATEX1 DATEX2

38361 12/01/2009 01/11/2005 12/01/2009

Number of cases read: 1 Number of cases listed: 1

No Idea wrote

I have a a date variable entered as string with two formats i.e. 12/01/2009 and 38361. How can I convert these to date variable with the format ddmmyyyy and width 20 in SPSS v.20? Requesting your help on this.