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