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.