The Altair Community is migrating to a new platform to provide a better experience for you. In preparation for the migration, the Altair Community is on read-only mode from October 28 - November 6, 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

Excel date number conversion

Telcontar120Telcontar120 RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn
edited November 2018 in Help

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!

 

 

Brian T.
Lindon Ventures 
Data Science Consulting from Certified RapidMiner Experts

Best Answer

  • sgenzersgenzer Administrator, Moderator, Employee-RapidMiner, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager
    Solution Accepted

    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(&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="144" resized="true" width="415" x="60" y="80">This process 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.**</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

Answers

  • Telcontar120Telcontar120 RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn

    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.

     

    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts
  • sgenzersgenzer Administrator, Moderator, Employee-RapidMiner, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    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.


    Scott

     

Sign In or Register to comment.