Excel date number conversion
Telcontar120
New Altair Community Member
I have a number from an Excel file that I'm pretty sure was originally a date/time stamp. But evidently Excel doesn't use the same numeric encoding for dates as RapidMiner (based on milliseconds since epoch)---so does anyone happen to have the correct conversion function for turning an Excel-encoded date/time number back into a date/time in RapidMiner? Thanks!
Tagged:
0
Best Answer
-
you mean something like this?
<?xml version="1.0" encoding="UTF-8"?><process version="8.0.001">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="6.0.002" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="generate_data_user_specification" compatibility="8.0.001" expanded="true" height="68" name="Generate Data by User Specification" width="90" x="112" y="238">
<list key="attribute_values">
<parameter key="date" value="42887"/>
</list>
<list key="set_additional_roles"/>
</operator>
<operator activated="true" class="subprocess" compatibility="8.0.001" expanded="true" height="82" name="Subprocess" width="90" x="246" y="238">
<process expanded="true">
<operator activated="true" class="generate_attributes" compatibility="8.0.001" expanded="true" height="82" name="Generate Attributes (2)" width="90" x="45" y="34">
<list key="function_descriptions">
<parameter key="date" value="date_add(date_parse("1/1/1900"),date-2,DATE_UNIT_DAY)"/>
</list>
</operator>
<connect from_port="in 1" to_op="Generate Attributes (2)" to_port="example set input"/>
<connect from_op="Generate Attributes (2)" from_port="example set output" to_port="out 1"/>
<portSpacing port="source_in 1" spacing="0"/>
<portSpacing port="source_in 2" spacing="0"/>
<portSpacing port="sink_out 1" spacing="0"/>
<portSpacing port="sink_out 2" spacing="0"/>
</process>
<description align="center" color="transparent" colored="false" width="126">CONVERT EXCEL NUMERICAL DATES</description>
</operator>
<connect from_op="Generate Data by User Specification" from_port="output" to_op="Subprocess" to_port="in 1"/>
<connect from_op="Subprocess" from_port="out 1" to_port="result 1"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
<description align="center" color="yellow" colored="false" height="144" resized="true" width="415" x="60" y="80">This process converts an attribute containing Excel &quot;numerical dates&quot; into the RapidMiner DATE/TIME format. For example, 42887 converts to June 1, 2017.<br/><br/>**Name your attribute &quot;date&quot; in order to run this process out of the box.**</description>
<description align="center" color="yellow" colored="false" height="51" resized="true" width="409" x="64" y="17">CONVERT EXCEL NUMERICAL DATES</description>
</process>
</operator>
</process>
Scott
3
Altair EmployeeAnswers
-
you mean something like this?
<?xml version="1.0" encoding="UTF-8"?><process version="8.0.001">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="6.0.002" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="generate_data_user_specification" compatibility="8.0.001" expanded="true" height="68" name="Generate Data by User Specification" width="90" x="112" y="238">
<list key="attribute_values">
<parameter key="date" value="42887"/>
</list>
<list key="set_additional_roles"/>
</operator>
<operator activated="true" class="subprocess" compatibility="8.0.001" expanded="true" height="82" name="Subprocess" width="90" x="246" y="238">
<process expanded="true">
<operator activated="true" class="generate_attributes" compatibility="8.0.001" expanded="true" height="82" name="Generate Attributes (2)" width="90" x="45" y="34">
<list key="function_descriptions">
<parameter key="date" value="date_add(date_parse("1/1/1900"),date-2,DATE_UNIT_DAY)"/>
</list>
</operator>
<connect from_port="in 1" to_op="Generate Attributes (2)" to_port="example set input"/>
<connect from_op="Generate Attributes (2)" from_port="example set output" to_port="out 1"/>
<portSpacing port="source_in 1" spacing="0"/>
<portSpacing port="source_in 2" spacing="0"/>
<portSpacing port="sink_out 1" spacing="0"/>
<portSpacing port="sink_out 2" spacing="0"/>
</process>
<description align="center" color="transparent" colored="false" width="126">CONVERT EXCEL NUMERICAL DATES</description>
</operator>
<connect from_op="Generate Data by User Specification" from_port="output" to_op="Subprocess" to_port="in 1"/>
<connect from_op="Subprocess" from_port="out 1" to_port="result 1"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
<description align="center" color="yellow" colored="false" height="144" resized="true" width="415" x="60" y="80">This process converts an attribute containing Excel &quot;numerical dates&quot; into the RapidMiner DATE/TIME format. For example, 42887 converts to June 1, 2017.<br/><br/>**Name your attribute &quot;date&quot; in order to run this process out of the box.**</description>
<description align="center" color="yellow" colored="false" height="51" resized="true" width="409" x="64" y="17">CONVERT EXCEL NUMERICAL DATES</description>
</process>
</operator>
</process>Scott
3 -
Perfect. So evidently: Excel date numerical = number of days since 1/1/1900??? OK Microsoft...
Anyway, thanks for the quick response on this one! Just what I needed.
1 -
yup that's exactly right. So you can put it on your list of "wonky things to remember when you're working with dates and times" list, like that Unix Epoch is number of MILLISECONDS since 1/1/1970.
Glad that helped.
Scott0