Conversion of date (Julian?) variable

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Conversion of date (Julian?) variable

Robert L

I have got this csv file which basically is an export from a measurement device, where the Time variable is formatted in the following way:

 

43111,0001041667 (where comma is the decimal separator, Swedish standard)

 

It has been possible to import the file into Excel, reformat the variable into a yy-mm-dd hh:mm:ss.ss format as it seems to be a Julian date format, followed by import into SPSS. But is there any way to convert this string into a working Date (and time) format directly? None of my attempts seem to work. Any suggestions? Going via Excel is of course possible, but it is frustrating not being able to do it directly in SPSS…

 

Robert

===================== 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
Robert Lundqvist
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of date (Julian?) variable

Maguin, Eugene

My first (and only) thought is that this is the number of unknown time units since time=0, whenever that might be. If the time unit were days since December 31, 1899 so that January 1, 1900 was day 1, which might be what excel uses, then 43111 divided by 365.25 is 118.03 years, so 2018, about mid-day Jan 11. If that date is plausible, then that’s the computation.

 

Gene Maguin

 

From: SPSSX(r) Discussion <[hidden email]> On Behalf Of Robert Lundqvist
Sent: Friday, January 25, 2019 5:33 AM
To: [hidden email]
Subject: Conversion of date (Julian?) variable

 

I have got this csv file which basically is an export from a measurement device, where the Time variable is formatted in the following way:

 

43111,0001041667 (where comma is the decimal separator, Swedish standard)

 

It has been possible to import the file into Excel, reformat the variable into a yy-mm-dd hh:mm:ss.ss format as it seems to be a Julian date format, followed by import into SPSS. But is there any way to convert this string into a working Date (and time) format directly? None of my attempts seem to work. Any suggestions? Going via Excel is of course possible, but it is frustrating not being able to do it directly in SPSS…

 

Robert

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

Re: Conversion of date (Julian?) variable

Bruce Weaver
Administrator
In reply to this post by Robert L
Hello Robert.  Your post prompted me to take a look at this syntax file,
which I wrote some time ago:

http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt

I'm not sure why you suspect the date to be Julian.  If the part before the
comma is stored as an Excel date (with the default 1900 base year), then
something like this ought to generate a proper date variable in SPSS:

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr (A20).
BEGIN DATA
"43111,0001041667"
END DATA.

COMPUTE #commapos = CHAR.INDEX(datestr,",").
COMPUTE date = NUMBER(CHAR.SUBSTR(datestr,1,#commapos-1),F8.0)* 86400 +
10010304000.
ALTER TYPE date(DATE11).
LIST.

OUTPUT:

datestr                     date
43111,0001041667     12-JAN-2018

Is 12-Jan-2018 the date you're getting using Excel?  

HTH.



Robert L wrote

> I have got this csv file which basically is an export from a measurement
> device, where the Time variable is formatted in the following way:
>
> 43111,0001041667 (where comma is the decimal separator, Swedish standard)
>
> It has been possible to import the file into Excel, reformat the variable
> into a yy-mm-dd hh:mm:ss.ss format as it seems to be a Julian date format,
> followed by import into SPSS. But is there any way to convert this string
> into a working Date (and time) format directly? None of my attempts seem
> to work. Any suggestions? Going via Excel is of course possible, but it is
> frustrating not being able to do it directly in SPSS...
>
> Robert
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

>  (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.

--
Sent from: http://spssx-discussion.1045642.n5.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
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of date (Julian?) variable

David Marso
Administrator
As a general practice, I try to avoid 'magic numbers'.  
We can deduce from the following that Bruce's magic number
"10010304000" is the beginning of the Gregorian calender.

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / x (F20).
BEGIN DATA
10010304000
END DATA.
COMPUTE X=CTIME.DAYS(10010304000).
FORMATS x (DATE11).
LIST.
          x
 
15-OCT-1582


I also prefer to use TIME.DAYS(1) rather than the constant 86400.  In this
case might as well
use the Excel Offset DATE.MDY(12,31,1899) and add the value of
TIME.DAYS(stuff before comma).
My question is what is the stuff AFTER the comma?
BTW:  When I read it into Open Office and Format as date after changing , to
. I get back
Jan 11, 2018 so we seem to be off by 1.  Hard to tell.  Maybe we need to
subtract one day?


NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr (A20).
BEGIN DATA
"43111,0001041667"
END DATA.
COMPUTE #comma =
NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0)    .
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma)).
ALTER TYPE date(DATE11).
LIST.
 
datestr                     date
 
43111,0001041667     12-JAN-2018
 
Number of cases read:  1    Number of cases listed:  1


This version subtracts one day to achieve agreement with Open Office/Excel

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr (A20).
BEGIN DATA
"43111,0001041667"
END DATA.
COMPUTE #comma =
NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0)    .
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma))-TIME.DAYS(1).
ALTER TYPE date(DATE11).
LIST.
 
datestr                     date
 
43111,0001041667     11-JAN-2018


Finally, we might as well skip the parsing and jump right to the jugular
-read two variables and just use the first one in the calculations.

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr junk.
BEGIN DATA
43111,0001041667
END DATA.
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(datestr))-TIME.DAYS(1).
ALTER TYPE date(DATE11).
LIST.

------------------------------------------------------------------------


Bruce Weaver wrote

> Hello Robert.  Your post prompted me to take a look at this syntax file,
> which I wrote some time ago:
>
> http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt
>
> I'm not sure why you suspect the date to be Julian.  If the part before
> the
> comma is stored as an Excel date (with the default 1900 base year), then
> something like this ought to generate a proper date variable in SPSS:
>
> NEW FILE.
> DATASET CLOSE ALL.
> DATA LIST LIST / datestr (A20).
> BEGIN DATA
> "43111,0001041667"
> END DATA.
>
> COMPUTE #commapos = CHAR.INDEX(datestr,",").
> COMPUTE date = NUMBER(CHAR.SUBSTR(datestr,1,#commapos-1),F8.0)* 86400 +
> 10010304000.
> ALTER TYPE date(DATE11).
> LIST.
>
> OUTPUT:
>
> datestr                     date
> 43111,0001041667     12-JAN-2018
>
> Is 12-Jan-2018 the date you're getting using Excel?  
>
> HTH.
>
>
>
> Robert L wrote
>> I have got this csv file which basically is an export from a measurement
>> device, where the Time variable is formatted in the following way:
>>
>> 43111,0001041667 (where comma is the decimal separator, Swedish standard)
>>
>> It has been possible to import the file into Excel, reformat the variable
>> into a yy-mm-dd hh:mm:ss.ss format as it seems to be a Julian date
>> format,
>> followed by import into SPSS. But is there any way to convert this string
>> into a working Date (and time) format directly? None of my attempts seem
>> to work. Any suggestions? Going via Excel is of course possible, but it
>> is
>> frustrating not being able to do it directly in SPSS...
>>
>> Robert
>>
>> =====================
>> To manage your subscription to SPSSX-L, send a message to
>
>> LISTSERV@.UGA
>
>>  (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

> bweaver@

> 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.
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

>  (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





-----
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?"
--
Sent from: http://spssx-discussion.1045642.n5.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
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?"
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of date (Julian?) variable

Jon Peck
The Mac and Windows versions of Excel differ in their base dates by one day.

On Fri, Jan 25, 2019 at 10:24 AM David Marso <[hidden email]> wrote:
As a general practice, I try to avoid 'magic numbers'. 
We can deduce from the following that Bruce's magic number
"10010304000" is the beginning of the Gregorian calender.

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / x (F20).
BEGIN DATA
10010304000
END DATA.
COMPUTE X=CTIME.DAYS(10010304000).
FORMATS x (DATE11).
LIST.
          x

15-OCT-1582


I also prefer to use TIME.DAYS(1) rather than the constant 86400.  In this
case might as well
use the Excel Offset DATE.MDY(12,31,1899) and add the value of
TIME.DAYS(stuff before comma).
My question is what is the stuff AFTER the comma?
BTW:  When I read it into Open Office and Format as date after changing , to
. I get back
Jan 11, 2018 so we seem to be off by 1.  Hard to tell.  Maybe we need to
subtract one day?


NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr (A20).
BEGIN DATA
"43111,0001041667"
END DATA.
COMPUTE #comma =
NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0)    .
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma)).
ALTER TYPE date(DATE11).
LIST.

datestr                     date

43111,0001041667     12-JAN-2018

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


This version subtracts one day to achieve agreement with Open Office/Excel

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr (A20).
BEGIN DATA
"43111,0001041667"
END DATA.
COMPUTE #comma =
NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0)    .
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma))-TIME.DAYS(1).
ALTER TYPE date(DATE11).
LIST.

datestr                     date

43111,0001041667     11-JAN-2018


Finally, we might as well skip the parsing and jump right to the jugular
-read two variables and just use the first one in the calculations.

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr junk.
BEGIN DATA
43111,0001041667
END DATA.
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(datestr))-TIME.DAYS(1).
ALTER TYPE date(DATE11).
LIST.

------------------------------------------------------------------------


Bruce Weaver wrote
> Hello Robert.  Your post prompted me to take a look at this syntax file,
> which I wrote some time ago:
>
> http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt
>
> I'm not sure why you suspect the date to be Julian.  If the part before
> the
> comma is stored as an Excel date (with the default 1900 base year), then
> something like this ought to generate a proper date variable in SPSS:
>
> NEW FILE.
> DATASET CLOSE ALL.
> DATA LIST LIST / datestr (A20).
> BEGIN DATA
> "43111,0001041667"
> END DATA.
>
> COMPUTE #commapos = CHAR.INDEX(datestr,",").
> COMPUTE date = NUMBER(CHAR.SUBSTR(datestr,1,#commapos-1),F8.0)* 86400 +
> 10010304000.
> ALTER TYPE date(DATE11).
> LIST.
>
> OUTPUT:
>
> datestr                     date
> 43111,0001041667     12-JAN-2018
>
> Is 12-Jan-2018 the date you're getting using Excel? 
>
> HTH.
>
>
>
> Robert L wrote
>> I have got this csv file which basically is an export from a measurement
>> device, where the Time variable is formatted in the following way:
>>
>> 43111,0001041667 (where comma is the decimal separator, Swedish standard)
>>
>> It has been possible to import the file into Excel, reformat the variable
>> into a yy-mm-dd hh:mm:ss.ss format as it seems to be a Julian date
>> format,
>> followed by import into SPSS. But is there any way to convert this string
>> into a working Date (and time) format directly? None of my attempts seem
>> to work. Any suggestions? Going via Excel is of course possible, but it
>> is
>> frustrating not being able to do it directly in SPSS...
>>
>> Robert
>>
>> =====================
>> To manage your subscription to SPSSX-L, send a message to
>
>> LISTSERV@.UGA
>
>>  (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

> bweaver@

> 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.
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

>  (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





-----
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?"
--
Sent from: http://spssx-discussion.1045642.n5.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


--
Jon K Peck
[hidden email]

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

Re: Conversion of date (Julian?) variable

Bruce Weaver
Administrator
In reply to this post by David Marso
I like that last one.  "Very nice", as Borat would say.  ;-)  


David Marso wrote

> --- snip ---
>
> Finally, we might as well skip the parsing and jump right to the jugular
> -read two variables and just use the first one in the calculations.
>
> NEW FILE.
> DATASET CLOSE ALL.
> DATA LIST LIST / datestr junk.
> BEGIN DATA
> 43111,0001041667
> END DATA.
> COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(datestr))-TIME.DAYS(1).
> ALTER TYPE date(DATE11).
> LIST.





-----
--
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.

--
Sent from: http://spssx-discussion.1045642.n5.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
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of date (Julian?) variable

Robert L
In reply to this post by Robert L
Many thanks for your suggestions, great support as always. The part after the comma which David asked about is actually time (hours, minutes and seconds down to tenths of seconds). So I simply added some parts:

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr (A20).
BEGIN DATA
'43104,0031076389'
END DATA.

COMPUTE #comma = NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0)    .
COMPUTE date = SUM(DATE.DMY(30,12,1899),TIME.DAYS(#comma)).
COMPUTE #rem=24*(NUMBER(datestr,F16.10)-#comma) /*Number of hours remaining after date component has been extracted*/.
COMPUTE #time0=3600*#rem /*The number of seconds*/.
COMPUTE time=date+#time0.
EXECUTE.

FORMATS date (DATE11) time(DATETIME24.1).

And as David spotted, for some reason it was necessary to subtract one day off the date. Thanks again!

Robert

-----Ursprungligt meddelande-----
Från: SPSSX(r) Discussion [mailto:[hidden email]] För David Marso
Skickat: den 25 januari 2019 18:24
Till: [hidden email]
Ämne: Re: Conversion of date (Julian?) variable

As a general practice, I try to avoid 'magic numbers'.  
We can deduce from the following that Bruce's magic number "10010304000" is the beginning of the Gregorian calender.

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / x (F20).
BEGIN DATA
10010304000
END DATA.
COMPUTE X=CTIME.DAYS(10010304000).
FORMATS x (DATE11).
LIST.
          x
 
15-OCT-1582


I also prefer to use TIME.DAYS(1) rather than the constant 86400.  In this case might as well use the Excel Offset DATE.MDY(12,31,1899) and add the value of TIME.DAYS(stuff before comma).
My question is what is the stuff AFTER the comma?
BTW:  When I read it into Open Office and Format as date after changing , to . I get back Jan 11, 2018 so we seem to be off by 1.  Hard to tell.  Maybe we need to subtract one day?


NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr (A20).
BEGIN DATA
"43111,0001041667"
END DATA.
COMPUTE #comma =
NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0)    .
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma)).
ALTER TYPE date(DATE11).
LIST.
 
datestr                     date
 
43111,0001041667     12-JAN-2018
 
Number of cases read:  1    Number of cases listed:  1


This version subtracts one day to achieve agreement with Open Office/Excel

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr (A20).
BEGIN DATA
"43111,0001041667"
END DATA.
COMPUTE #comma =
NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0)    .
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma))-TIME.DAYS(1).
ALTER TYPE date(DATE11).
LIST.
 
datestr                     date
 
43111,0001041667     11-JAN-2018


Finally, we might as well skip the parsing and jump right to the jugular -read two variables and just use the first one in the calculations.

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / datestr junk.
BEGIN DATA
43111,0001041667
END DATA.
COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(datestr))-TIME.DAYS(1).
ALTER TYPE date(DATE11).
LIST.

------------------------------------------------------------------------


Bruce Weaver wrote

> Hello Robert.  Your post prompted me to take a look at this syntax
> file, which I wrote some time ago:
>
> http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt
>
> I'm not sure why you suspect the date to be Julian.  If the part
> before the comma is stored as an Excel date (with the default 1900
> base year), then something like this ought to generate a proper date
> variable in SPSS:
>
> NEW FILE.
> DATASET CLOSE ALL.
> DATA LIST LIST / datestr (A20).
> BEGIN DATA
> "43111,0001041667"
> END DATA.
>
> COMPUTE #commapos = CHAR.INDEX(datestr,",").
> COMPUTE date = NUMBER(CHAR.SUBSTR(datestr,1,#commapos-1),F8.0)* 86400
> + 10010304000.
> ALTER TYPE date(DATE11).
> LIST.
>
> OUTPUT:
>
> datestr                     date
> 43111,0001041667     12-JAN-2018
>
> Is 12-Jan-2018 the date you're getting using Excel?  
>
> HTH.
>
>
>
> Robert L wrote
>> I have got this csv file which basically is an export from a
>> measurement device, where the Time variable is formatted in the following way:
>>
>> 43111,0001041667 (where comma is the decimal separator, Swedish
>> standard)
>>
>> It has been possible to import the file into Excel, reformat the
>> variable into a yy-mm-dd hh:mm:ss.ss format as it seems to be a
>> Julian date format, followed by import into SPSS. But is there any
>> way to convert this string into a working Date (and time) format
>> directly? None of my attempts seem to work. Any suggestions? Going
>> via Excel is of course possible, but it is frustrating not being able
>> to do it directly in SPSS...
>>
>> Robert
>>
>> =====================
>> To manage your subscription to SPSSX-L, send a message to
>
>> LISTSERV@.UGA
>
>>  (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

> bweaver@

> 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.
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

>  (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





-----
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?"
--
Sent from: http://spssx-discussion.1045642.n5.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
Robert Lundqvist
Reply | Threaded
Open this post in threaded view
|

Re: Conversion of date (Julian?) variable

PRogman
You have actually stumbled on a long term ignored bug in Excel's date
handling. 1900 is handled as a leap year, but was not, as only centennials
evenly divided by 400 should be a leap year, according to the gregorian
calendar. That is the explanation to the 1 day difference between SPSS and
Excel. According to Microsoft documentation the date base is identical in
Windows and Mac versions, but I do not know if the Mac version handles the
year 1900 differently. There was an option (Apple, IIRC), to set the date
base to 1904 jan 1 to avoid leap year troubles. I guess very few Excel users
handle old dates nowadays, or demand time resolution below years.

Excel dates are based on integer values where DMY.DATE(1, 1 , 1900) is day 1
(day 0 is undefined...) and DMY.DATE(2, 1, 1900) is day 2. Time values are
fractions of a day where 0 is midnight and 0.5 is 12 PM.

See
https://en.wikipedia.org/wiki/Year_1900_problem
https://support.office.com/en-us/article/date-function-e36c0c8c-4104-49da-ab83-82328b832349
https://support.office.com/en-us/article/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457 

My version on this conversion follows. As Excel datevalue fractions are part
of a day no further handling is necessary. SPSS will interpret the
resolution correctly.

Also remember there is a way to temporary handle decimal values with a
foreign decimal sign...

/PR

*------------------------------------.
NEW FILE.

PRESERVE.
* Handle the decimal number with a comma!
SET DECIMAL = COMMA.

DATA LIST LIST /
xlDateValue (F15.10).
BEGIN DATA
43111,0001041667
0
1
60
39448
END DATA.
RESTORE.

COMPUTE #xlStartDate = DATE.DMY(31, 12, 1899).
COMPUTE spssDate     = #xlStartDate + (xlDateValue - (xlDateValue GT 60)) *
TIME.DAYS(1).
* spss handles 1900 leap day correctly.
COMPUTE spss1900ly   = DATE.DMY(29,  2, 1900).

EXECUTE.
FORMATS spssDate spss1900ly (YMDHMS40.5).

LIST xlDateValue spssDate .
*------------------------------------.

     xlDateValue                                 spssDate
 
43111,0001041667                2018-01-11 00:00:09.00000
    0,0000000000                1899-12-31 00:00:00.00000
    1,0000000000                1900-01-01 00:00:00.00000
   60,0000000000                1900-03-01 00:00:00.00000
39448,0000000000                2008-01-01 00:00:00.00000
 
 Number of cases read:  5    Number of cases listed:  5
*------------------------------------.




--
Sent from: http://spssx-discussion.1045642.n5.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