Re: small string issue
- To: mathgroup at smc.vnet.net
- Subject: [mg131784] Re: small string issue
- From: Bill Rowe <readnews at sbcglobal.net>
- Date: Thu, 3 Oct 2013 22:26:03 -0400 (EDT)
- Delivered-to: l-mathgroup@mail-archive0.wolfram.com
- Delivered-to: l-mathgroup@wolfram.com
- Delivered-to: mathgroup-outx@smc.vnet.net
- Delivered-to: mathgroup-newsendx@smc.vnet.net
On 10/3/13 at 12:35 AM, fgutiers2002 at yahoo.com (Francisco Gutierrez)
wrote:
>I am importing into Mathematica a big number of dates. For example:
>unasfechas = {11/06/1998, 21/01/2010, 12/10/2001, 20/07/2009,
>02/02/2002, 02/01/2004, 19/11/2001, 14/02/2010, 26/12/2009,
>20/10/1996, 09/07/2006, 01/01/1997, 30/08/1998, 15/02/2006,
>13/12/2009, 25/09/2002, 29/06/2009, 02/04/2010, 22/02/2001,
>01/04/2010}
>These dates come from Excel files, and are brought into Mathematica
>not as strings but as numbers. I want to transform them into strings
>to be able to apply functions like DateList, etc.
>In principle, this should be easy. This example, taken from the
>documentation, is simple and clear: ToString[x^2, InputForm]
>Which returns a workable string.
>However: ToString[unasfechas[[1]], InputForm]
>returns 11/11988
Right. Notice
In[2]:= unasfechas[[1]]
Out[2]= 11/11988
That is, if you do not make 11/06/1998 a string *before*
importing it to Mathematica, Mathematica sees this as an
expression consisting of three values and two division
operations and evaluates that expression to 11/11988.
You really have only two choices to correctly import dates from
Excel into Mathematica. You can import the dates as strings and
make use of Mathematica's functions to deal with dates or you
can reformat the date cells in Excel to the general format which
will change the dates to numbers.
Using strings you could do:
In[3]:= DateList["11/06/1998"]
Out[3]= {1998,11,6,0,0,0.}
which will generate a warning message since the original string
could be Nov 6, 1998 or June 11, 1998
Alternatively if you enter 11/6/1998 into Excel then reformat
the cell to the general format you will get the integer 36105.
Excel stores dates as the number of days from a reference time.
You can convert this to a date in Mathematica as follows:
DateList[(excelDateNo-2)86400]
To demonstrate:
In[4]:= DateList[(36105 - 2) 86400]
Out[4]= {1998,11,6,0,0,0.}
The offset of 2 is to adjust for the difference in what a date
of 0 means in both Excel and Mathematica. The multiplicative
factor (86400) is the number of seconds in a day.
My preference is to reformat the date cells as general in Excel
since this avoids ambiguity.