SPSSX Discussion

Excel to SPSS dates garbled

classic Classic list List threaded Threaded
♦
♦
 5 messages Options
Stirkey, Vicki
Reply | Threaded
Open this post in threaded view
♦
♦
|

Excel to SPSS dates garbled

Stirkey, Vicki

Lately, when I am reading Excel data into SPSS (v. 15) I get dates that are erroneous. See the following example-

As seen in original Excel file-

1/1/2008 0:00           1/31/2008 0:00         

2/1/2008 0:00           2/29/2008 0:00         

3/1/2008 0:00           5/31/2008 0:00         

3/1/2008 0:00                                   

1/1/2008 0:00                                    

2/1/2008 0:08                                   

3/1/2008 0:08                                   

4/1/2008 0:08                                   

2/1/2008 0:08                                   

3/1/2008 0:08                                   

4/1/2008 0:08                                   

4/16/2008 0:00                      4/23/2008 0:00         

 

What shows up in SPSS-

 

39448.000012                      39478                  

39479.000012                      39507                  

39508.000012                      39599                  

39508.000012                                              

39448.000012                                              

39479.005556                                              

39508.005556                                              

39539.005556                                              

39479.005556                                              

39508.005556                                              

39539.005556                                              

39554.000012                      39561                  

 

If I save the file in txt. format and read that into SPSS, I get correct dates.

Has anyone ever experienced this? Do you have any ideas on how to resolve this?

Thanks in advance for any input!

 

Vicki L. Stirkey

Program Analyst 3

OMHSAS

Office of Mental Health and Substance Abuse Services

Division of Systems Management

717-705-8198

Fax: 717-772-6737

 
Francien Berndsen
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel to SPSS dates garbled

Francien Berndsen
See Raynald Levesque's great website www.spsstools.net:

*To convert the 5 digits numbers to SPSS dates, try this.
DATA LIST LIST /var1(F8.0) var2(A6).
BEGIN DATA
1       "1"
36604   "36604"
25000   "25000"
END DATA.
LIST.

COMPUTE date1=date.dmy(1,1,1900)+ (var1-2)*60*60*24.
COMPUTE date2=date.dmy(1,1,1900)+ (NUMBER(var2,F8.0)-2)*60*60*24.
FORMATS date1 date2 (ADATE12).
LIST.

Francien

> Lately, when I am reading Excel data into SPSS (v. 15) I get dates that
> are erroneous. See the following example-
> As seen in original Excel file-
> 1/1/2008 0:00           1/31/2008 0:00
> 2/1/2008 0:00           2/29/2008 0:00
> 3/1/2008 0:00           5/31/2008 0:00
> 3/1/2008 0:00
> 1/1/2008 0:00
> 2/1/2008 0:08
> 3/1/2008 0:08
> 4/1/2008 0:08
> 2/1/2008 0:08
> 3/1/2008 0:08
> 4/1/2008 0:08
> 4/16/2008 0:00                      4/23/2008 0:00
>
> What shows up in SPSS-
>
> 39448.000012                      39478
> 39479.000012                      39507
> 39508.000012                      39599
> 39508.000012
> 39448.000012
> 39479.005556
> 39508.005556
> 39539.005556
> 39479.005556
> 39508.005556
> 39539.005556
> 39554.000012                      39561
>
> If I save the file in txt. format and read that into SPSS, I get correct
> dates.
> Has anyone ever experienced this? Do you have any ideas on how to resolve
> this?
> Thanks in advance for any input!
>
> Vicki L. Stirkey
> Program Analyst 3
> OMHSAS
> Office of Mental Health and Substance Abuse Services
> Division of Systems Management
> 717-705-8198
> Fax: 717-772-6737
>
>

=====================
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
David Futrell
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel to SPSS dates garbled

David Futrell
I was never able to solve this problem without writing code (like Raynald's below). I can report, however, that with Version 18, importing Excel 2007 dates behaves much better.


--- On Fri, 1/22/10, Francien Berndsen <[hidden email]> wrote:

From: Francien Berndsen <[hidden email]>
Subject: Re: Excel to SPSS dates garbled
To: [hidden email]
Date: Friday, January 22, 2010, 8:45 AM

See Raynald Levesque's great website www.spsstools.net:

*To convert the 5 digits numbers to SPSS dates, try this.
DATA LIST LIST /var1(F8.0) var2(A6).
BEGIN DATA
1       "1"
36604   "36604"
25000   "25000"
END DATA.
LIST.

COMPUTE date1=date.dmy(1,1,1900)+ (var1-2)*60*60*24.
COMPUTE date2=date.dmy(1,1,1900)+ (NUMBER(var2,F8.0)-2)*60*60*24.
FORMATS date1 date2 (ADATE12).
LIST.

Francien

> Lately, when I am reading Excel data into SPSS (v. 15) I get dates that
> are erroneous. See the following example-
> As seen in original Excel file-
> 1/1/2008 0:00           1/31/2008 0:00
> 2/1/2008 0:00           2/29/2008 0:00
> 3/1/2008 0:00           5/31/2008 0:00
> 3/1/2008 0:00
> 1/1/2008 0:00
> 2/1/2008 0:08
> 3/1/2008 0:08
> 4/1/2008 0:08
> 2/1/2008 0:08
> 3/1/2008 0:08
> 4/1/2008 0:08
> 4/16/2008 0:00                      4/23/2008 0:00
>
> What shows up in SPSS-
>
> 39448.000012                      39478
> 39479.000012                      39507
> 39508.000012                      39599
> 39508.000012
> 39448.000012
> 39479.005556
> 39508.005556
> 39539.005556
> 39479.005556
> 39508.005556
> 39539.005556
> 39554.000012                      39561
>
> If I save the file in txt. format and read that into SPSS, I get correct
> dates.
> Has anyone ever experienced this? Do you have any ideas on how to resolve
> this?
> Thanks in advance for any input!
>
> Vicki L. Stirkey
> Program Analyst 3
> OMHSAS
> Office of Mental Health and Substance Abuse Services
> Division of Systems Management
> 717-705-8198
> Fax: 717-772-6737
>
>

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (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
Maguin, Eugene
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel to SPSS dates garbled

Maguin, Eugene
In reply to this post by Stirkey, Vicki
Vicki,

I've noticed that sometimes us people are careless when we put data into
excel and all cells in a column do not have the same format. Perhaps this
might have been true here. Check that first. Then what Lucien described.

Gene Maguin


Lately, when I am reading Excel data into SPSS (v. 15) I get dates that are
erroneous. See the following example-

As seen in original Excel file-
1/1/2008 0:00           1/31/2008 0:00
2/1/2008 0:00           2/29/2008 0:00
3/1/2008 0:00           5/31/2008 0:00
3/1/2008 0:00
1/1/2008 0:00
2/1/2008 0:08
3/1/2008 0:08
4/1/2008 0:08
2/1/2008 0:08
3/1/2008 0:08
4/1/2008 0:08
4/16/2008 0:00                      4/23/2008 0:00



What shows up in SPSS-
39448.000012                      39478
39479.000012                      39507
39508.000012                      39599
39508.000012
39448.000012
39479.005556
39508.005556
39539.005556
39479.005556
39508.005556
39539.005556
39554.000012                      39561


If I save the file in txt. format and read that into SPSS, I get correct
dates.
Has anyone ever experienced this? Do you have any ideas on how to resolve
this?
Thanks in advance for any input!



Vicki L. Stirkey
Program Analyst 3
OMHSAS
Office of Mental Health and Substance Abuse Services
Division of Systems Management
717-705-8198
Fax: 717-772-6737

=====================
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
Bruce Weaver
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel to SPSS dates garbled

Bruce Weaver
Administrator
In reply to this post by Francien Berndsen
Francien Berndsen wrote
See Raynald Levesque's great website www.spsstools.net:

--- snip ---
I've also got some notes & syntax related to this issue on my SPSS page:

   http://sites.google.com/a/lakeheadu.ca/bweaver/Home/statistics/spss/my-spss-page

Scroll down to the "Importing Data" section.

--
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/).
Free forum by Nabble Edit this page