Date Format
Locked
 
|
Hello,
I have a dataset in which dates/times have imported as (e.g) 40254.893727, from 3/17/2010 9:26:58 PM. How could I convert to a format that is recognized in SPSS? Thank you. ===================== 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 |
|
|
Hilda,
Imported from where? Spss? Excel? SAS? Statistics Canada? Gene Maguin -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of hilda Sent: Sunday, May 16, 2010 8:28 PM To: [hidden email] Subject: Date Format Hello, I have a dataset in which dates/times have imported as (e.g) 40254.893727, from 3/17/2010 9:26:58 PM. How could I convert to a format that is recognized in SPSS? Thank you. ===================== 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 ===================== 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 |
|
|
In reply to this post by hilda-14
Hilda-
Those look like Excel serial number dates. This syntax should do the trick for you: compute SPSS_Date_Time = date.mdy(01,01,1900) + Time.days( Excel_Serial_Value - 2 ). formats SPSS_Date_Time (date11). execute. Just change "Excel_Serial_Value" to whatever variable of yours has "40254.893727". Cheers, -Eric Background on the date conversion: Excel measures dates as the number of days after Jan 1, 1900 and counts 1900 as if it were a leap year (which it wasn't). That was the easiest way for Microsoft to make Excel compatible with Lotus 1-2-3. SPSS measures dates as the number of seconds since midnight, October 14, 1582 - the first day of the Gregorian Calendar. The first part of the compute generates the number of seconds from Oct 14, 1582 to Jan 1, 1900. The second part generates the number of seconds after Jan 1, 1900 - if Excel correctly assumed that 1900 was not a leap year, then it would only be necessary to subtract 1. For reference, if you have an SPSS serial value that you want to convert to an Excel serial value -- if you copy and paste output from SPSS to Excel. Here's an Excel formula that will give convert it -- you'll still need to format the result as a date (Keyboard shortcut Ctrl + Shift + 3) =(B3/86400)-115859 -----Original Message----- From: hilda [mailto:[hidden email]] Sent: Sunday, May 16, 2010 7:28 PM Subject: Date Format Hello, I have a dataset in which dates/times have imported as (e.g) 40254.893727, from 3/17/2010 9:26:58 PM. How could I convert to a format that is recognized in SPSS? Thank you. ===================== 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 |
Administrator
|
Good stuff, Eric. I've added a link to your post on my SPSS page, where I have a couple files on issues that can arise when one imports date variables from Excel. See the "Importing Data" section at:
https://sites.google.com/a/lakeheadu.ca/bweaver/Home/statistics/spss/my-spss-page Cheers, Bruce
--
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/). |
|
|
Note that if the excel file comes from mac, the date count by default
starts 1904. I believe there is an option in mac excel to set windoze dates. Or I guess edit your syntax. regards, Ian Ian D. Martin, Ph.D. Tsuji Laboratory University of Waterloo Dept. of Environment & Resource Studies On 18 May, 2010, at 3:18 PM, Bruce Weaver wrote: > Good stuff, Eric. I've added a link to your post on my SPSS page, > where I > have a couple files on issues that can arise when one imports date > variables > from Excel. See the "Importing Data" section at: > > > https://sites.google.com/a/lakeheadu.ca/bweaver/Home/statistics/ > spss/my-spss-page > > Cheers, > Bruce > > > > Langston, Eric wrote: >> >> Hilda- >> >> Those look like Excel serial number dates. This syntax should do the >> trick for you: >> >> compute SPSS_Date_Time = date.mdy(01,01,1900) + Time.days( >> Excel_Serial_Value - 2 ). >> formats SPSS_Date_Time (date11). >> execute. >> >> Just change "Excel_Serial_Value" to whatever variable of yours has >> "40254.893727". >> >> Cheers, >> -Eric >> >> >> >> Background on the date conversion: >> Excel measures dates as the number of days after Jan 1, 1900 and >> counts >> 1900 as if it were a leap year (which it wasn't). That was the >> easiest >> way for Microsoft to make Excel compatible with Lotus 1-2-3. >> SPSS measures dates as the number of seconds since midnight, >> October 14, >> 1582 - the first day of the Gregorian Calendar. >> >> The first part of the compute generates the number of seconds from >> Oct >> 14, 1582 to Jan 1, 1900. The second part generates the number of >> seconds after Jan 1, 1900 - if Excel correctly assumed that 1900 >> was not >> a leap year, then it would only be necessary to subtract 1. >> >> For reference, if you have an SPSS serial value that you want to >> convert >> to an Excel serial value -- if you copy and paste output from SPSS to >> Excel. Here's an Excel formula that will give convert it -- you'll >> still need to format the result as a date (Keyboard shortcut Ctrl + >> Shift + 3) >> =(B3/86400)-115859 >> >> >> >> >> -----Original Message----- >> From: hilda [mailto:[hidden email]] >> Sent: Sunday, May 16, 2010 7:28 PM >> Subject: Date Format >> >> Hello, >> >> I have a dataset in which dates/times have imported as (e.g) >> 40254.893727, >> from 3/17/2010 9:26:58 PM. How could I convert to a format that is >> recognized in SPSS? >> >> Thank you. >> >> ===================== >> 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 > [hidden email] > http://sites.google.com/a/lakeheadu.ca/bweaver/ > "When all else fails, RTFM." > > NOTE: My Hotmail account is not monitored regularly. > To send me an e-mail, please use the address shown above. > -- > View this message in context: http://old.nabble.com/Date-Format- > tp28584832p28599696.html > Sent from the SPSSX Discussion mailing list archive at Nabble.com. > > ===================== > 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 ===================== 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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |