Re: Converting XML DATEEVENT to Mathematica AbsoluteTime

*To*: mathgroup at smc.vnet.net*Subject*: [mg115868] Re: Converting XML DATEEVENT to Mathematica AbsoluteTime*From*: "Hans Michel" <hmichel at cox.net>*Date*: Sun, 23 Jan 2011 05:36:49 -0500 (EST)

In[1]:= ToDate[(40555.76528 - 2)*(60*60*24)] Out[1]= {2011, 1, 12, 18, 22, 0.192} Version 7 of Mathematica This has been address in several different ways in this group. See this thread http://groups.google.com/group/comp.soft-sys.math.mathematica/browse_frm/thr ead/78c88ae874b8492c/9ddbdcda03330fc1?hl=en&q= I made a mistake then and used +2 instead of -2. I don't have access to my email that I sent, but I think I would have use -2 not +2. But we know that "=" get obfuscated sometimes. Again to re-iterate the problem of DateSerial is a Lotus 1-2-3 problem that Microsoft copied. But most people blame Microsoft for it. If you copy an erroneous formula verbatim for the purposes of keeping the business market share, then ... Hans -----Original Message----- From: Ingolf Dahl [mailto:ingolf.dahl at telia.com] Sent: Saturday, January 22, 2011 2:21 AM To: mathgroup at smc.vnet.net Subject: [mg115868] [mg115829] Converting XML DATEEVENT to Mathematica AbsoluteTime To MathGroup, The readings of my blood glucose monitor can be transferred into an XML file in my PC, one record for each reading. The date and time for each record is stored in a DATEEVENT variable, with a value that might be 40555.76528 or something similar. Microsoft Excel can read such a XML file, and translate the DATEEVENT value into date and time, if the corresponding column is changed to a date format. Then 40555.76528 corresponds to 2011-01-12 18:22:00. I tried to google on this to find the correct formula to transfer the DATEEVENT variable into Mathematica AbsoluteTime, but found nothing comprehensible. Then I started writing a question to MathGroup, but looking closer at it, I found the solution. If the date is later than March 1, 1900, the formula is DateeventToAbsoluteTime [dateevent_] /; dateevent >= 61 := Round[(dateevent - 2)*24*3600] The reason for the minus two term is that the creator of the DATEEVENT code included the nonexistent dates Jan 0, 1900 and Feb 29, 1900 in the definition range. (Year 1900 was not a leap year.) If we want to include January and February 1900 in the conversion also, we have to complicate the code a little: DateeventToAbsoluteTime[dateevent_] /; And[dateevent >= 1, Floor[dateevent] != 60] := Round[(dateevent - If[dateevent < 61, 1, 2])*24*3600] Maybe someone else (or I myself) will google for this, and will find this information useful. Ingolf Dahl --------------------------------------------------------------------------- Ingolf Dahl e-mail: ingolf.dahl at telia.com www.familydahl.se\mathematica ---------------------------------------------------------------------------