Converting Excel numerical dates to RapidMiner Date-Time attributes

sgenzer

This is just a quick handy tool that I have used countless times - it converts those very annoying five digit "numerical dates" in Excel to a standardized RapidMiner date-time attribute.

For example, the Excel numerical date "42887" converts to "June 1, 2017".

Here's the process if you want to look at it. It's also on the Community Repository for easy access as a building block.

<?xml version="1.0" encoding="UTF-8"?><process version="8.1.003">
  <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.1.003" expanded="true" height="68" name="Generate Data by User Specification" width="90" x="246" 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.1.003" expanded="true" height="82" name="Subprocess" width="90" x="380" y="238">
        <process expanded="true">
          <operator activated="true" class="generate_attributes" compatibility="8.1.003" 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(&quot;1/1/1900&quot;),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="125" resized="true" width="568" x="78" y="402">This building block converts an attribute containing Excel &amp;quot;numerical dates&amp;quot; into the RapidMiner DATE/TIME format. For example, 42887 converts to June 1, 2017.&lt;br&gt;&lt;br&gt;**Name your attribute &amp;quot;date&amp;quot; in order to run this process out of the box. You can test it by connecting the &amp;quot;Generate Data&amp;quot; operator to the subprocess and the output to results**</description>
    </process>
  </operator>
</process>

[Fun fact - those numerical dates in Excel are the number of days since January 1, 1900.]

Scott

davidb

does this take into consideration the lotus 1-2-3 leap year bug?
https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year

sgenzer

hmm interesting question @davidb. No idea. There must be an easy way to check...