Difficulty with dates

Difficulty with dates

Post by Philip Feel » Sat, 19 Jul 2003 03:35:35



When importing data from an Excel file, SPSS doesn't seem to handle
dates very well. I've found that the dates do not transfer correctly
(or not at all) with recent versions of Excel. The only one I've been
able to get to work consistently is Excel v. 3.0. But lately even this
has been failing. This could be because the original data file has the
dates formatting differently (i.e. mm-dd-yyyy and mm/dd/yyyy). I guess
I'll need to get that consistent before I worry about importing the file.

When I import the file the date fields are formatted as strings. If I
try to change this to date all the data disappears. Obviously, I'm doing
something wrong...

Thanks for any help.

Phil

 
 
 

Difficulty with dates

Post by Rick Olive » Sat, 19 Jul 2003 04:53:29


As long as all the values in the same column are recognized as some kind of
legitimate date format in Excel, then SPSS should read them as dates. I
think by default SPSS will display these dates in dd-mm-yyyy form, but the
original dates in Excel don't have to be in that form -- they just have to
be in a format that Excel recognizes as a date. If, however, anything in the
column is not a date, then SPSS will read the variable in string format.


Quote:> When importing data from an Excel file, SPSS doesn't seem to handle
> dates very well. I've found that the dates do not transfer correctly
> (or not at all) with recent versions of Excel. The only one I've been
> able to get to work consistently is Excel v. 3.0. But lately even this
> has been failing. This could be because the original data file has the
> dates formatting differently (i.e. mm-dd-yyyy and mm/dd/yyyy). I guess
> I'll need to get that consistent before I worry about importing the file.

> When I import the file the date fields are formatted as strings. If I
> try to change this to date all the data disappears. Obviously, I'm doing
> something wrong...

> Thanks for any help.

> Phil


 
 
 

Difficulty with dates

Post by Bruce Weave » Sat, 19 Jul 2003 06:33:21



> When importing data from an Excel file, SPSS doesn't seem to handle
> dates very well. I've found that the dates do not transfer correctly
> (or not at all) with recent versions of Excel. The only one I've been
> able to get to work consistently is Excel v. 3.0. But lately even this
> has been failing. This could be because the original data file has the
> dates formatting differently (i.e. mm-dd-yyyy and mm/dd/yyyy). I guess
> I'll need to get that consistent before I worry about importing the file.

> When I import the file the date fields are formatted as strings. If I
> try to change this to date all the data disappears. Obviously, I'm doing
> something wrong...

> Thanks for any help.

> Phil

This won't likely help for your present situation.  But in
future, you might want to ask the folks you're working with
to enter dates as 3 numeric variables in separate columns
(Day, Month, and Year).  This makes importing into SPSS
problem-free, and you can easily convert the 3 numeric
variables to a DATE variable in SPSS, like this:

compute datevar = date.dmy(day,month,year).
exe.

Cheers,
Bruce
--
Bruce Weaver

www.angelfire.com/wv/bwhomedir/

 
 
 

Difficulty with dates

Post by Fre » Sat, 19 Jul 2003 18:17:51


If you import the date as a text variable, rather than as a date
variable, you can run
comp date=date.dmy(number(substr(datestr,4,2),f2),number(substr(datestr,1,2),f2),number(substr(datestr,7,4),f4)).
format date (date11).
where datestr is the date in text format.

which should get you the date you want, in the format of dd-mmm-yyyy.



> > When importing data from an Excel file, SPSS doesn't seem to handle
> > dates very well. I've found that the dates do not transfer correctly
> > (or not at all) with recent versions of Excel. The only one I've been
> > able to get to work consistently is Excel v. 3.0. But lately even this
> > has been failing. This could be because the original data file has the
> > dates formatting differently (i.e. mm-dd-yyyy and mm/dd/yyyy). I guess
> > I'll need to get that consistent before I worry about importing the file.

> > When I import the file the date fields are formatted as strings. If I
> > try to change this to date all the data disappears. Obviously, I'm doing
> > something wrong...

> > Thanks for any help.

> > Phil

> This won't likely help for your present situation.  But in
> future, you might want to ask the folks you're working with
> to enter dates as 3 numeric variables in separate columns
> (Day, Month, and Year).  This makes importing into SPSS
> problem-free, and you can easily convert the 3 numeric
> variables to a DATE variable in SPSS, like this:

> compute datevar = date.dmy(day,month,year).
> exe.

> Cheers,
> Bruce

 
 
 

1. Difficulty in comparing dates in Excel 5

I use Excel 5.0a and WfW3.11.

In a sheet I would like to compare dates.
These are entered through EditBoxes in a Dialog.

In Column A the formula for the date is always:
  dlgstart.EditBoxes("EBDate").Text = Format(CDate(Int(Now)), "dd-mm-yy")
This Editbox is filled with the current date
.
In Column B the date is entered by the user.
  dlgnext.EditBoxes("EBPlandate").Text = Format("", "dd-mm-yy")
This Editbox is initially blank.

When the macro runs I enter eg. the following dates in the Editboxes
        A           B
     19-01-98   19-01-98
     19-01-98   18-01-98
     19-01-98   08-04-98        (april 8)
     19-01-98   04-08-98        (august 4)

When I format the sheet with NumberFormat = "dd-mm-yy" the columns
look like this:
        A           B
     19-01-98   19-01-98
     19-01-98   18-01-98
     19-01-98   04-08-98        (august 4, but must be april 8)
     19-01-98   08-04-98        (april 8, but must be august 4)
It seems that Excel swaps the day and month when the day has a number
that could be a month (01-12).


the columns look like this:
        A           B
     19-01-98   19-01-98
     19-01-98   18-01-98
     19-01-98   36011
     19-01-98   35893

In the International settings in the Windows Control Panel the
Country    = Netherlands
Language   = English (International)
Date format= 19-01-98

In the Excel/Options/Module General the International settings are
the Current Settings.
The Date/Time format = 12/31/99 11:59:51 PM.

What do I need to do to solve this problem?

2. Word count in LaTeX

3. release dates(delivery dates) for xbox

4. Evaluate() and getting inside Variants

5. AnastasiaWeb.com.ua | Russian Bride Dating | Russian Dating Agencies | Russian Women Bride

6. How to use CallNtPowerInformation!

7. TOP DATING TIPS TO ENCOURAGE WOMEN FOR DATING

8. Business

9. Need to change date to current date is the contents in a row is changed