Times and dates to convert

[John F Hall]

It's not often I need any help, but there's always a first time!

 

I have been sent a data set for a series of incidents comprising time, date and other data for each incident.  The dates came in Excel and I fed them into PASW 18, which converted the slashes in the dates to dots. 

 

    Time     Date

 

    1251     26.04.2010
    2201     26.04.2010
    2201     26.04.2010
    2201     26.04.2010
    1548     01.05.2010

I've never dealt with date/time data before, but I now need to derive from these a series of:

 

Day of Week (Mon - Sun etc)

Days since day one or day <n>

Month

 

I've managed to create Year, Month, Week and Day with the Date/Time Wizard, but these are all numeric (Week is numbered from Jan 2010).  I'm going to need analysis by name of month and name of day of week as well as days since ..., but I'll persevere with that myself.

 

I couldn't find a way to convert the 24-hour times to hr:mm format so did a workaround to generate hour and minutes separately.

 

* 24-hour clock conversion.
comp hour = trunc (time/100) .
comp minutes = time - hour * 100 .
format hour minutes (f2.0) .exec.

Data View shows them as integers, but SPSS copied them as below:

 

12.0	51.0
22.0	1.0
22.0	1.0
22.0	1.0
15.0	48.0

 

How do I now get these into hh:mm format?  Then how do I combine date and time into a single variable?

 

TIA

 

John Hall

[John F Hall]

Bruce,

 

Thanks.  Done.  Only 50 test cases so far, but probably thousands more to come.  Have requested future times to be sent in hh:mm format.  Best to have everything ready and working before the data deluge.

 

time date       timevar  date_and_time

1251 26.04.2010 12:51    26-APR-2010 12:51

2201 26.04.2010 22:01    26-APR-2010 22:01

2201 26.04.2010 22:01    26-APR-2010 22:01

2201 26.04.2010 22:01    26-APR-2010 22:01

1548 01.05.2010 15:48    01-MAY-2010 15:48

etc., etc.

 

John

----- Original Message -----

From: [hidden email]

To: [hidden email]

Cc: [hidden email]

Sent: Monday, June 28, 2010 1:33 PM

Subject: Re: Times and dates to convert

Hi John.  I've not seen your post on the mailing list yet, so I'll just respond here.  See below.

data list list/ time (f5.0) date (edate).
begin data
    1251     26.04.2010
    2201     26.04.2010
    2201     26.04.2010
    2201     26.04.2010
    1548     01.05.2010
end data.

numeric timevar(time5) / date_and_time(datetime17).
compute #h = trunc(time/100).
compute #m = time - #h*100.
compute timevar = time.hms(#h,#m,0).
compute date_and_time = date + timevar.
list.

Cheers,
Bruce

p.s. - Pity about England going out, but I don't think there's any doubt they were outclassed.

On Mon, Jun 28, 2010 at 6:43 AM, John F Hall <[hidden email]> wrote:

It's not often I need any help, but there's always a first time!

 

I have been sent a data set for a series of incidents comprising time, date and other data for each incident.  The dates came in Excel and I fed them into PASW 18, which converted the slashes in the dates to dots. 

 

    Time     Date

 

    1251     26.04.2010
    2201     26.04.2010
    2201     26.04.2010
    2201     26.04.2010
    1548     01.05.2010

I've never dealt with date/time data before, but I now need to derive from these a series of:

 

Day of Week (Mon - Sun etc)

Days since day one or day <n>

Month

 

I've managed to create Year, Month, Week and Day with the Date/Time Wizard, but these are all numeric (Week is numbered from Jan 2010).  I'm going to need analysis by name of month and name of day of week as well as days since ..., but I'll persevere with that myself.

 

I couldn't find a way to convert the 24-hour times to hr:mm format so did a workaround to generate hour and minutes separately.

 

* 24-hour clock conversion.
comp hour = trunc (time/100) .
comp minutes = time - hour * 100 .
format hour minutes (f2.0) .exec.

Data View shows them as integers, but SPSS copied them as below:

 

12.0	51.0
22.0	1.0
22.0	1.0
22.0	1.0
15.0	48.0

 

How do I now get these into hh:mm format?  Then how do I combine date and time into a single variable?

 

TIA

 

John Hall

--
Bruce Weaver
Research Associate, Centre for Research on Safe Driving, Lakehead University
Assistant Professor of Biostatistics, Human Sciences Division
Northern Ontario School of Medicine, West Campus
955 Oliver Road, MS-2006, Thunder Bay, ON  P7B 5E1

Tel:  [phone]      E-mail:  [hidden email]
Fax:  [phone]      Web:   http://sites.google.com/a/lakeheadu.ca/bweaver/

[Maguin, Eugene]

John,

I seldom use excel and don't have much experence with it. It wasn't clear
from your message but it sounds like time and date are separate
variables/fields. I wonder if you can do something in excel with the colum
formatting to make things easier going into spss. What I'm thinking of
specifically is checking to see if time and date are formated in excel as
date/time. I think there are several choices (or maybe that is in access).

Gene Maguin



________________________________

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
John F Hall
Sent: Monday, June 28, 2010 6:44 AM
To: [hidden email]
Subject: Times and dates to convert


It's not often I need any help, but there's always a first time!

I have been sent a data set for a series of incidents comprising time, date
and other data for each incident.  The dates came in Excel and I fed them
into PASW 18, which converted the slashes in the dates to dots.

    Time     Date

    1251     26.04.2010
    2201     26.04.2010
    2201     26.04.2010
    2201     26.04.2010
    1548     01.05.2010

I've never dealt with date/time data before, but I now need to derive from
these a series of:

Day of Week (Mon - Sun etc)
Days since day one or day <n>
Month

I've managed to create Year, Month, Week and Day with the Date/Time Wizard,
but these are all numeric (Week is numbered from Jan 2010).  I'm going to
need analysis by name of month and name of day of week as well as days since
..., but I'll persevere with that myself.

I couldn't find a way to convert the 24-hour times to hr:mm format so did a
workaround to generate hour and minutes separately.

* 24-hour clock conversion.
comp hour = trunc (time/100) .
comp minutes = time - hour * 100 .
format hour minutes (f2.0) .exec.

Data View shows them as integers, but SPSS copied them as below:

12.0     51.0
22.0     1.0
22.0     1.0
22.0     1.0
15.0     48.0

How do I now get these into hh:mm format?  Then how do I combine date and
time into a single variable?

TIA

John Hall

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