SPSSX Discussion

EXCEL DATES - error message

Classic

List

Threaded

14 messages Options

thara vardhan-2

EXCEL DATES - error message

Dear List Members

Sometime back Lombardo, Barbara had asked for help with the following
problem:

>Would someone please tell me how to get Excel dates into SPSS. It
>shows as a date in Excel, 05/06/1954 but comes into SPSS as 19849.

and I think it was Richard Ristow who provided the tested solution as

NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.

COMPUTE SPSSDate = DATE.MDY(01,01,1900)
+ TIME.DAYS(ExcelNum)
- TIME.DAYS(1).
LIST.

I am experiencing the same problem in reading a date variable in SPSS
version 17.0 from an Excel file.

I have a date variable Pbirthdate in Excel (4/06/1985) which is reading as
31201in SPSS.

I tried to apply the above syntax:

But I am getting an error message:

NUMERIC SPSSDate (ADATE10).
COMPUTE SPSSDate = DATE.MDY(01,01,1900)
+ TIME.DAYS(Pbirthdate)
- TIME.DAYS(1).

>Error # 4310 in column 42. Text: )
>The numeric argument required for the function specified was not
supplied.
>This command not executed.
execute.

Can you please help me understand the error message?

Any help is much appreciated.

thanks
regards
Thara Vardhan
Senior Statistician

Thara Vardhan
Senior Statistician
Performance Improvement & Planning
NSW Police
Tel: (02) 8835-8526
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

The information contained in this email is intended for the named recipient(s)
only. It may contain private, confidential, copyright or legally privileged
information. If you are not the intended recipient or you have received this
email by mistake, please reply to the author and delete this email immediately.
You must not copy, print, forward or distribute this email, nor place reliance
on its contents. This email and any attachment have been virus scanned. However,
you are requested to conduct a virus scan as well. No liability is accepted
for any loss or damage resulting from a computer virus, or resulting from a delay
or defect in transmission of this email or any attached file. This email does not
constitute a representation by the NSW Police Force unless the author is legally
entitled to do so.

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

Art Kendall

Re: EXCEL DATES - error message

go to variables view and see if Pbirthdate is a numeric field and that you have spelled it correctly.

Art Kendall
Social Research Consultants

Thara Vardhan wrote:

> Dear List Members
>
> Sometime back Lombardo, Barbara had asked for help with the following
> problem:
>
>
>> Would someone please tell me how to get Excel dates into SPSS. It
>> shows as a date in Excel, 05/06/1954 but comes into SPSS as 19849.
>>
>
> and I think it was Richard Ristow who provided the tested solution as
>
> NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.
>
> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> + TIME.DAYS(ExcelNum)
> - TIME.DAYS(1).
> LIST.
>
> I am experiencing the same problem in reading a date variable in SPSS
> version 17.0 from an Excel file.
>
> I have a date variable Pbirthdate in Excel (4/06/1985) which is reading as
> 31201in SPSS.
>
> I tried to apply the above syntax:
>
> But I am getting an error message:
>
> NUMERIC SPSSDate (ADATE10).
> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> + TIME.DAYS(Pbirthdate)
> - TIME.DAYS(1).
>
>
>> Error # 4310 in column 42. Text: )
>> The numeric argument required for the function specified was not
>>
> supplied.
>
>> This command not executed.
>>
> execute.
>
> Can you please help me understand the error message?
>
> Any help is much appreciated.
>
> thanks
> regards
> Thara Vardhan
> Senior Statistician
>
>
>
>
>
>
>
>
>
> Thara Vardhan
> Senior Statistician
> Performance Improvement & Planning
> NSW Police
> Tel: (02) 8835-8526
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>
> The information contained in this email is intended for the named recipient(s)
> only. It may contain private, confidential, copyright or legally privileged
> information. If you are not the intended recipient or you have received this
> email by mistake, please reply to the author and delete this email immediately.
> You must not copy, print, forward or distribute this email, nor place reliance
> on its contents. This email and any attachment have been virus scanned. However,
> you are requested to conduct a virus scan as well. No liability is accepted
> for any loss or damage resulting from a computer virus, or resulting from a delay
> or defect in transmission of this email or any attached file. This email does not
> constitute a representation by the NSW Police Force unless the author is legally
> entitled to do so.
>
> =====================
> 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
>
>
>

Art Kendall
Social Research Consultants

Ian Martin-2

Re: EXCEL DATES - error message

I haven't seen any mention of OS/platform in this thread, but thought
it worth noting that mac and pc have different excel date
representations. Mac is 1904 and pc is 1900, I think? I know I've
had to deal with this when moving excel files with dates between
these OSs. Perhaps it may also be an issue with bringing cross
platform files into SPSS from excel?

regards,
Ian.

Ian D. Martin, Ph.D.

Tsuji Laboratory
University of Waterloo
Dept. of Environment & Resource Studies

On 01 Dec, 2008, at 7:07 AM, Art Kendall wrote:

> go to variables view and see if Pbirthdate is a numeric field and
> that you have spelled it correctly.
>
>
> Art Kendall
> Social Research Consultants
>
>
>
> Thara Vardhan wrote:
>> Dear List Members
>>
>> Sometime back Lombardo, Barbara had asked for help with the following
>> problem:
>>
>>
>>> Would someone please tell me how to get Excel dates into SPSS. It
>>> shows as a date in Excel, 05/06/1954 but comes into SPSS as 19849.
>>>
>>
>> and I think it was Richard Ristow who provided the tested solution as
>>
>> NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.
>>
>> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
>> + TIME.DAYS(ExcelNum)
>> - TIME.DAYS(1).
>> LIST.
>>
>> I am experiencing the same problem in reading a date variable in SPSS
>> version 17.0 from an Excel file.
>>
>> I have a date variable Pbirthdate in Excel (4/06/1985) which is
>> reading as
>> 31201in SPSS.
>>
>> I tried to apply the above syntax:
>>
>> But I am getting an error message:
>>
>> NUMERIC SPSSDate (ADATE10).
>> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
>> + TIME.DAYS(Pbirthdate)
>> - TIME.DAYS(1).
>>
>>
>>> Error # 4310 in column 42. Text: )
>>> The numeric argument required for the function specified was not
>>>
>> supplied.
>>
>>> This command not executed.
>>>
>> execute.
>>
>> Can you please help me understand the error message?
>>
>> Any help is much appreciated.
>>
>> thanks
>> regards
>> Thara Vardhan
>> Senior Statistician
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Thara Vardhan
>> Senior Statistician
>> Performance Improvement & Planning
>> NSW Police
>> Tel: (02) 8835-8526
>> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>> _ _ _ _ _ _ _ _
>>
>> The information contained in this email is intended for the named
>> recipient(s)
>> only. It may contain private, confidential, copyright or legally
>> privileged
>> information. If you are not the intended recipient or you have
>> received this
>> email by mistake, please reply to the author and delete this email
>> immediately.
>> You must not copy, print, forward or distribute this email, nor
>> place reliance
>> on its contents. This email and any attachment have been virus
>> scanned. However,
>> you are requested to conduct a virus scan as well. No liability
>> is accepted
>> for any loss or damage resulting from a computer virus, or
>> resulting from a delay
>> or defect in transmission of this email or any attached file. This
>> email does not
>> constitute a representation by the NSW Police Force unless the
>> author is legally
>> entitled to do so.
>>
>> =====================
>> 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

Peck, Jon

Re: EXCEL DATES - error message

The SPSS code knows about this Excel oddity. In fact, you can control what origin Excel uses even on Windows, so the SPSS code on both platforms has to deal with this.

Most likely, there is something in the Excel sheet that is causing SPSS to treat these values as plain numbers, not dates. Remember that a column in SPSS has to have a consistent format. Look for something in the Excel date column that is not actually a date.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Ian Martin
Sent: Monday, December 01, 2008 8:26 AM
To: [hidden email]
Subject: Re: [SPSSX-L] EXCEL DATES - error message

I haven't seen any mention of OS/platform in this thread, but thought
it worth noting that mac and pc have different excel date
representations. Mac is 1904 and pc is 1900, I think? I know I've
had to deal with this when moving excel files with dates between
these OSs. Perhaps it may also be an issue with bringing cross
platform files into SPSS from excel?

regards,
Ian.

Ian D. Martin, Ph.D.

Tsuji Laboratory
University of Waterloo
Dept. of Environment & Resource Studies

On 01 Dec, 2008, at 7:07 AM, Art Kendall wrote:

thara vardhan-2

Re: EXCEL DATES - error message - reply

Hi Jon and Art

Thank you so much for responding to my question.

Before I sent my problem to the list members I checked the variable
pbirthdate in the excel file.

1. The cell format is a "date" but the type is in the format
*14/03/2001. The message that excel shows is the following: Date formats
diplay date and time serial numbers as date values except for items that
have an asterik (*), applied formats do not switch date orders with the
operating system.

2. I tried to change the format within excel before exporting to SPSS but
I was not able to do so.

3. As for your suggestion Art, to check if the variable format is a
'Numeric'. The variable is exported as a 'String'. If I change it to a
numeric the values change to missing values.

I think there in lies the problem but I am not sure how to modify the
syntax when it is string rather than a numeric.

4. Also I extract the data from our mainframe system using EDW queries
(SQL). The EDW only provides for exporting data thru excel and no other
software. I cannot make any changes within EDW.

Lastly the only way I could solve the problem is to save the data as an
Excel.4 worksheet - then when I read it in SPSS - it works perfectly.
However one limitation of saving it as an Excel version4 is that we cannot
save multiple sheets of data within a single file.

many thanks
Thara Vardhan
Senior Statistician
Performance Improvement & Planning

"Peck, Jon" <[hidden email]>
Sent by: "SPSSX(r) Discussion" <[hidden email]>
02/12/2008 04:08
Please respond to
"Peck, Jon" <[hidden email]>

To
[hidden email]
cc

Subject
Re: EXCEL DATES - error message

The SPSS code knows about this Excel oddity. In fact, you can control
what origin Excel uses even on Windows, so the SPSS code on both platforms
has to deal with this.

Most likely, there is something in the Excel sheet that is causing SPSS to
treat these values as plain numbers, not dates. Remember that a column in
SPSS has to have a consistent format. Look for something in the Excel
date column that is not actually a date.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Ian Martin
Sent: Monday, December 01, 2008 8:26 AM
To: [hidden email]
Subject: Re: [SPSSX-L] EXCEL DATES - error message

I haven't seen any mention of OS/platform in this thread, but thought
it worth noting that mac and pc have different excel date
representations. Mac is 1904 and pc is 1900, I think? I know I've
had to deal with this when moving excel files with dates between
these OSs. Perhaps it may also be an issue with bringing cross
platform files into SPSS from excel?

regards,
Ian.

Ian D. Martin, Ph.D.

Tsuji Laboratory
University of Waterloo
Dept. of Environment & Resource Studies

On 01 Dec, 2008, at 7:07 AM, Art Kendall wrote:

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

All mail is subject to content scanning for possible violation of NSW
Police Force
Electronic Messaging Policy. All NSW Police Force employees are required
to
familiarise themselves with the content of the policy, found under
Policies on the
NSW Police Force Intranet.

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

The information contained in this email is intended for the named recipient(s)
only. It may contain private, confidential, copyright or legally privileged
information. If you are not the intended recipient or you have received this
email by mistake, please reply to the author and delete this email immediately.
You must not copy, print, forward or distribute this email, nor place reliance
on its contents. This email and any attachment have been virus scanned. However,
you are requested to conduct a virus scan as well. No liability is accepted
for any loss or damage resulting from a computer virus, or resulting from a delay
or defect in transmission of this email or any attached file. This email does not
constitute a representation by the NSW Police Force unless the author is legally
entitled to do so.

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

Albert-Jan Roskam

Re: EXCEL DATES - error message - reply

Hi Thara,

Will this syntax do the trick?

data list free / funnydate (a12).
begin data
*14/03/2001
end data.

numeric spssdate (edate30).
compute spssdate = date.dmy(
number(substr(funnydate, 2, 2), n2),
number(substr(funnydate, 5, 2), n2),
number(substr(funnydate, 8, 4), n4)
).
exe.

Cheers!!
Albert-Jan

--- On Tue, 12/2/08, Thara Vardhan <[hidden email]> wrote:

> From: Thara Vardhan <[hidden email]>
> Subject: Re: EXCEL DATES - error message - reply
> To: [hidden email]
> Date: Tuesday, December 2, 2008, 12:14 AM
> Hi Jon and Art
>
> Thank you so much for responding to my question.
>
> Before I sent my problem to the list members I checked the
> variable
> pbirthdate in the excel file.
>
> 1. The cell format is a "date" but the type is
> in the format
> *14/03/2001. The message that excel shows is the following:
> Date formats
> diplay date and time serial numbers as date values except
> for items that
> have an asterik (*), applied formats do not switch date
> orders with the
> operating system.
>
> 2. I tried to change the format within excel before
> exporting to SPSS but
> I was not able to do so.
>
> 3. As for your suggestion Art, to check if the variable
> format is a
> 'Numeric'. The variable is exported as a
> 'String'. If I change it to a
> numeric the values change to missing values.
>
> I think there in lies the problem but I am not sure how to
> modify the
> syntax when it is string rather than a numeric.
>
> 4. Also I extract the data from our mainframe system using
> EDW queries
> (SQL). The EDW only provides for exporting data thru excel
> and no other
> software. I cannot make any changes within EDW.
>
> Lastly the only way I could solve the problem is to save
> the data as an
> Excel.4 worksheet - then when I read it in SPSS - it works
> perfectly.
> However one limitation of saving it as an Excel version4 is
> that we cannot
> save multiple sheets of data within a single file.
>
> many thanks
> Thara Vardhan
> Senior Statistician
> Performance Improvement & Planning
>
>
>
>
>
> "Peck, Jon" <[hidden email]>
> Sent by: "SPSSX(r) Discussion"
> <[hidden email]>
> 02/12/2008 04:08
> Please respond to
> "Peck, Jon" <[hidden email]>
>
>
> To
> [hidden email]
> cc
>
> Subject
> Re: EXCEL DATES - error message
>
>
>
>
>
>
> The SPSS code knows about this Excel oddity. In fact, you
> can control
> what origin Excel uses even on Windows, so the SPSS code on
> both platforms
> has to deal with this.
>
> Most likely, there is something in the Excel sheet that is
> causing SPSS to
> treat these values as plain numbers, not dates. Remember
> that a column in
> SPSS has to have a consistent format. Look for something
> in the Excel
> date column that is not actually a date.
>
> HTH,
> Jon Peck
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]]
> On Behalf Of
> Ian Martin
> Sent: Monday, December 01, 2008 8:26 AM
> To: [hidden email]
> Subject: Re: [SPSSX-L] EXCEL DATES - error message
>
> I haven't seen any mention of OS/platform in this
> thread, but thought
> it worth noting that mac and pc have different excel date
> representations. Mac is 1904 and pc is 1900, I think? I
> know I've
> had to deal with this when moving excel files with dates
> between
> these OSs. Perhaps it may also be an issue with bringing
> cross
> platform files into SPSS from excel?
>
> regards,
> Ian.
>
> Ian D. Martin, Ph.D.
>
> Tsuji Laboratory
> University of Waterloo
> Dept. of Environment & Resource Studies
>
>
> On 01 Dec, 2008, at 7:07 AM, Art Kendall wrote:
>
> > go to variables view and see if Pbirthdate is a
> numeric field and
> > that you have spelled it correctly.
> >
> >
> > Art Kendall
> > Social Research Consultants
> >
> >
> >
> > Thara Vardhan wrote:
> >> Dear List Members
> >>
> >> Sometime back Lombardo, Barbara had asked for help
> with the following
> >> problem:
> >>
> >>
> >>> Would someone please tell me how to get Excel
> dates into SPSS. It
> >>> shows as a date in Excel, 05/06/1954 but
> comes into SPSS as 19849.
> >>>
> >>
> >> and I think it was Richard Ristow who provided the
> tested solution as
> >>
> >> NUMERIC SPSSDate (ADATE10) /* or any SPSS date
> format */.
> >>
> >> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> >> + TIME.DAYS(ExcelNum)
> >> - TIME.DAYS(1).
> >> LIST.
> >>
> >> I am experiencing the same problem in reading a
> date variable in SPSS
> >> version 17.0 from an Excel file.
> >>
> >> I have a date variable Pbirthdate in Excel
> (4/06/1985) which is
> >> reading as
> >> 31201in SPSS.
> >>
> >> I tried to apply the above syntax:
> >>
> >> But I am getting an error message:
> >>
> >> NUMERIC SPSSDate (ADATE10).
> >> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> >> + TIME.DAYS(Pbirthdate)
> >> - TIME.DAYS(1).
> >>
> >>
> >>> Error # 4310 in column 42. Text: )
> >>> The numeric argument required for the function
> specified was not
> >>>
> >> supplied.
> >>
> >>> This command not executed.
> >>>
> >> execute.
> >>
> >> Can you please help me understand the error
> message?
> >>
> >> Any help is much appreciated.
> >>
> >> thanks
> >> regards
> >> Thara Vardhan
> >> Senior Statistician
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> Thara Vardhan
> >> Senior Statistician
> >> Performance Improvement & Planning
> >> NSW Police
> >> Tel: (02) 8835-8526
> >> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> _ _ _ _ _ _ _ _
> >> _ _ _ _ _ _ _ _
> >>
> >> The information contained in this email is
> intended for the named
> >> recipient(s)
> >> only. It may contain private, confidential,
> copyright or legally
> >> privileged
> >> information. If you are not the intended
> recipient or you have
> >> received this
> >> email by mistake, please reply to the author and
> delete this email
> >> immediately.
> >> You must not copy, print, forward or distribute
> this email, nor
> >> place reliance
> >> on its contents. This email and any attachment
> have been virus
> >> scanned. However,
> >> you are requested to conduct a virus scan as well.
> No liability
> >> is accepted
> >> for any loss or damage resulting from a computer
> virus, or
> >> resulting from a delay
> >> or defect in transmission of this email or any
> attached file. This
> >> email does not
> >> constitute a representation by the NSW Police
> Force unless the
> >> author is legally
> >> entitled to do so.
> >>
> >> =====================
> >> 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
>
> =====================
> 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
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> _ _ _ _ _ _ _
> _ _ _ _ _
>
> All mail is subject to content scanning for possible
> violation of NSW
> Police Force
> Electronic Messaging Policy. All NSW Police Force employees
> are required
> to
> familiarise themselves with the content of the policy,
> found under
> Policies on the
> NSW Police Force Intranet.
>
>
>
>
>
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> _ _ _ _ _ _ _ _ _ _ _
>
> The information contained in this email is intended for the
> named recipient(s)
> only. It may contain private, confidential, copyright or
> legally privileged
> information. If you are not the intended recipient or you
> have received this
> email by mistake, please reply to the author and delete
> this email immediately.
> You must not copy, print, forward or distribute this email,
> nor place reliance
> on its contents. This email and any attachment have been
> virus scanned. However,
> you are requested to conduct a virus scan as well. No
> liability is accepted
> for any loss or damage resulting from a computer virus, or
> resulting from a delay
> or defect in transmission of this email or any attached
> file. This email does not
> constitute a representation by the NSW Police Force unless
> the author is legally
> entitled to do so.
>
> =====================
> 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

Richard Ristow

Re: EXCEL DATES - error message

In reply to this post by thara vardhan-2

At 10:20 PM 11/30/2008, Thara Vardhan wrote:

>Sometime back Lombardo, Barbara had asked for help with the following
>problem:
>
>>Would someone please tell me how to get Excel dates into SPSS. It
>>shows as a date in Excel, 05/06/1954 but comes into SPSS as 19849.
>
>and I think it was Richard Ristow who provided the tested solution as
>
>NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.
>
>COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> + TIME.DAYS(ExcelNum)
> - TIME.DAYS(1).
>
>I am experiencing the same problem in reading a date variable in
>SPSS version 17.0 from an Excel file. I have a date variable
>Pbirthdate in Excel (4/06/1985) which is reading as 31201 in SPSS.
>
>I tried to apply the above syntax, but I am getting an error message:
>
>NUMERIC SPSSDate (ADATE10).
>COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> + TIME.DAYS(Pbirthdate)
> - TIME.DAYS(1).
>
>>Error # 4310 in column 42. Text: )
>>The numeric argument required for the function specified was not supplied.

And continuing, at 06:14 PM 12/1/2008, Thara Vardhan wrote:

>3. As for your suggestion Art, to check if the variable format is a
>'Numeric'. The variable is exported as a 'String'. If I change it to
>a numeric the values change to missing values.

I haven't time to test this, but try, then, using the NUMBER function in SPSS:

Instead of
COMPUTE SPSSDate = DATE.MDY(01,01,1900)
+ TIME.DAYS(Pbirthdate)
- TIME.DAYS(1).
try
COMPUTE SPSSDate = DATE.MDY(01,01,1900)
+ TIME.DAYS(NUMBER(Pbirthdate,F5))
- TIME.DAYS(1).

That leaves unresolved many ins and outs of Excel dates, versions,
and column formats; but, see if it's enough to solve your problem.

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

Peck, Jon

Re: EXCEL DATES - error message

Off list we have determined that missing values in the date column are being represented with text values in Excel. Fixing that lets SPSS import the date values in the expected way.

BTW, a useful trick for diagnosing odd behavior with Excel imports is to create a column of values in Excel like =TYPE(A1), say, if A is the column that imports oddly. This produces a column of type codes. You can then easily scan or tabulate this column to see if the cell type varies.

In general, it's better to fix the Excel sheet rather than patch things up after importing, although both approaches can work.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Richard Ristow
Sent: Tuesday, December 02, 2008 7:04 AM
To: [hidden email]
Subject: Re: [SPSSX-L] EXCEL DATES - error message

At 10:20 PM 11/30/2008, Thara Vardhan wrote:

Ian Martin-2

Re: EXCEL DATES - error message

Excellent tip John, thank you! Would explicitly formatting (in
Excel) the variable values as numbers have overridden the text
values? I suppose it would depend upon what character the missing
values were coded as.

regards,
Ian

Ian D. Martin, Ph.D.

Tsuji Laboratory
University of Waterloo
Dept. of Environment & Resource Studies

On 02 Dec, 2008, at 9:36 AM, Peck, Jon wrote:

> Off list we have determined that missing values in the date column
> are being represented with text values in Excel. Fixing that lets
> SPSS import the date values in the expected way.
>
> BTW, a useful trick for diagnosing odd behavior with Excel imports
> is to create a column of values in Excel like =TYPE(A1), say, if A
> is the column that imports oddly. This produces a column of type
> codes. You can then easily scan or tabulate this column to see if
> the cell type varies.
>
> In general, it's better to fix the Excel sheet rather than patch
> things up after importing, although both approaches can work.
>
> HTH,
> Jon Peck
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On
> Behalf Of Richard Ristow
> Sent: Tuesday, December 02, 2008 7:04 AM
> To: [hidden email]
> Subject: Re: [SPSSX-L] EXCEL DATES - error message
>
> At 10:20 PM 11/30/2008, Thara Vardhan wrote:
>
>> Sometime back Lombardo, Barbara had asked for help with the following
>> problem:
>>
>>> Would someone please tell me how to get Excel dates into SPSS. It
>>> shows as a date in Excel, 05/06/1954 but comes into SPSS as 19849.
>>
>> and I think it was Richard Ristow who provided the tested solution as
>>
>> NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.
>>
>> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
>> + TIME.DAYS(ExcelNum)
>> - TIME.DAYS(1).
>>
>> I am experiencing the same problem in reading a date variable in
>> SPSS version 17.0 from an Excel file. I have a date variable
>> Pbirthdate in Excel (4/06/1985) which is reading as 31201 in SPSS.
>>
>> I tried to apply the above syntax, but I am getting an error message:
>>
>> NUMERIC SPSSDate (ADATE10).
>> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
>> + TIME.DAYS(Pbirthdate)
>> - TIME.DAYS(1).
>>
>>> Error # 4310 in column 42. Text: )
>>> The numeric argument required for the function specified was not
>>> supplied.
>
> And continuing, at 06:14 PM 12/1/2008, Thara Vardhan wrote:
>
>> 3. As for your suggestion Art, to check if the variable format is a
>> 'Numeric'. The variable is exported as a 'String'. If I change it to
>> a numeric the values change to missing values.
>
> I haven't time to test this, but try, then, using the NUMBER
> function in SPSS:
>
> Instead of
> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> + TIME.DAYS(Pbirthdate)
> - TIME.DAYS(1).
> try
> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> + TIME.DAYS(NUMBER(Pbirthdate,F5))
> - TIME.DAYS(1).
>
> That leaves unresolved many ins and outs of Excel dates, versions,
> and column formats; but, see if it's enough to solve your problem.
>
> =====================
> 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

Peck, Jon

Re: EXCEL DATES - error message

I suppose it would depend on the values. You can assign a numeric format to a cell in Excel that contains a nonnumeric value such as "abc", but this seems to have no effect on anything, and SPSS still treats it as a string. Type seems to be stronger than format.

You can use the Excel value() function to extract the numeric value of a cell, so you could create a new column of =value(...). If the value can't be converted to a number, the value is #VALUE, and SPSS will import that as sysmis. This is probably counterproductive in most cases, though. For dates, value() gives the underlying numerical value in Excel. That imports fine into SPSS as a number, but you would then have to figure out how to recreate it as an SPSS date.

For missing values in particular, it's better on the Excel side to represent them as =NA() or to just leave the cell empty. Both of those come into SPSS as sysmis.

Regards,
Jon

-----Original Message-----
From: Ian Martin [mailto:[hidden email]]
Sent: Tuesday, December 02, 2008 8:02 AM
To: Peck, Jon; SPSSX(r) Discussion
Subject: Re: EXCEL DATES - error message

Excellent tip John, thank you! Would explicitly formatting (in
Excel) the variable values as numbers have overridden the text
values? I suppose it would depend upon what character the missing
values were coded as.

regards,
Ian

Ian D. Martin, Ph.D.

Tsuji Laboratory
University of Waterloo
Dept. of Environment & Resource Studies

On 02 Dec, 2008, at 9:36 AM, Peck, Jon wrote:

Jeremy

Re: EXCEL DATES - error message

In reply to this post by thara vardhan-2

I just had this error message in spss, when using syntax to sum several
variables together.

I checked my variables' data types and found one was a string variable, not
a numeric variable. When I fixed that, everything worked fine. SPSS has
some pretty useless error codes !

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

David Marso-2

Re: EXCEL DATES - error message

In reply to this post by thara vardhan-2

" I just had this error message in spss, when using syntax to sum several variables together.

I checked my variables' data types and found one was a string variable, not
a numeric variable. When I fixed that, everything worked fine. SPSS has
some pretty useless error codes ! "

Do you think it would be helpful to post the exact error message along with what you were doing. In my long experience with SPSS the error messages are usually spot on and quite useful. Clarify the foundations of your mini rant. Alternatively I could summon my eSPSS or Internetelepathy and tentatively conclude operator error as the primary cause.

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

PRogman

Re: EXCEL DATES - error message

In reply to this post by thara vardhan-2

Even if the question is old, and maybe not an SPSS issue... I am curious:
In my Windows Excel v2016 I get the following ExcelDate (I prefer the format
YYYY-MM-DD, or YYY-MMM-DD):

DateSerial ExcelDate ExpectedDate ExpFormat
31201 1985-JUN-3 4/06/1985 DD/MM/YYYY
19849 1954-MAY-5 05/06/1954 MM/DD/YYYY
i.e 1 day difference.

Using Richard Bristow's transformation, which adds 1900-JAN-01, gets the
expected result.
A strange thing is that Excel accepts 1900-01-00 as dateSerial 0, which
would be 1899-12-31.
Then there is the old mistake in Excel dates of 1900 not being a leap year:
1900-FEB-29 is valid (dateserial 60). It rarely poses a problem, except when
exporting dates to other systems.
Still I do not get the OP's dates...
???
/PR

https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487

Thara Vardhan wrote

> ...
>>shows as a date in Excel, 05/06/1954 but comes into SPSS as 19849.
> ...
> I have a date variable Pbirthdate in Excel (4/06/1985) which is reading as
> 31201 in SPSS.
> ...
> Richard Ristow who provided the tested solution as
> NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.
> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
> + TIME.DAYS(ExcelNum)
> - TIME.DAYS(1).
> ...

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

Re: EXCEL DATES - error message

Administrator

Because of all these kinds of issues, I always advise anyone who is using
Excel for data entry to enter dates as 3 separate variables, Day (DD), Month
(MM) and Year (YYYY). I reckon it is often much easier for the end user in
any package to combine those 3 pieces into a date variable (if one is
needed) than it is to convert Excel dates. ;-)

By the way, some of my efforts to wrestle with Excel dates were recorded in
a syntax file I wrote back in 2007. In case it's helpful to anyone, it can
be downloaded from this page:

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

Look for "importing_excel_dates.SPS".

Cheers,
Bruce

PRogman wrote

> Even if the question is old, and maybe not an SPSS issue... I am curious:
> In my Windows Excel v2016 I get the following ExcelDate (I prefer the
> format
> YYYY-MM-DD, or YYY-MMM-DD):
>
> DateSerial ExcelDate ExpectedDate ExpFormat
> 31201 1985-JUN-3 4/06/1985 DD/MM/YYYY
> 19849 1954-MAY-5 05/06/1954 MM/DD/YYYY
> i.e 1 day difference.
>
> Using Richard Bristow's transformation, which adds 1900-JAN-01, gets the
> expected result.
> A strange thing is that Excel accepts 1900-01-00 as dateSerial 0, which
> would be 1899-12-31.
> Then there is the old mistake in Excel dates of 1900 not being a leap
> year:
> 1900-FEB-29 is valid (dateserial 60). It rarely poses a problem, except
> when
> exporting dates to other systems.
> Still I do not get the OP's dates...
> ???
> /PR
>
> https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
> https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487
>
>
>
>
> Thara Vardhan wrote
>> ...
>>>shows as a date in Excel, 05/06/1954 but comes into SPSS as 19849.
>> ...
>> I have a date variable Pbirthdate in Excel (4/06/1985) which is reading
>> as
>> 31201 in SPSS.
>> ...
>> Richard Ristow who provided the tested solution as
>> NUMERIC SPSSDate (ADATE10) /* or any SPSS date format */.
>> COMPUTE SPSSDate = DATE.MDY(01,01,1900)
>> + TIME.DAYS(ExcelNum)
>> - TIME.DAYS(1).
>> ...
>
>
>
>
>
> --
> 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

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

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