The Altair Community is migrating to a new platform to provide a better experience for you. The RapidMiner Community will merge with the Altair Community at the same time. In preparation for the migration, both communities are on read-only mode from July 15th - July 24th, 2024. Technical support via cases will continue to work as is. For any urgent requests from Students/Faculty members, please submit the form linked here.
Options

Converting Excel numerical dates to RapidMiner Date-Time attributes

sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager
edited August 2019 in Knowledge Base
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

Comments

Sign In or Register to comment.