RapidMiner

RM Certified Expert
RM Certified Expert

Excel date number conversion

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 - www.lindonventures.com
Analytics Consulting by Certified RapidMiner Analysts
3 REPLIES
Community Manager Community Manager
Community Manager
Solution

Re: Excel date number conversion

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>

Smiley Happy

 

Scott

Scott Genzer
Senior Community Manager
RapidMiner, Inc.
RM Certified Expert
RM Certified Expert

Re: Excel date number conversion

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 - www.lindonventures.com
Analytics Consulting by Certified RapidMiner Analysts
Highlighted
Community Manager Community Manager
Community Manager

Re: Excel date number conversion

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. Smiley Happy

 

Glad that helped.


Scott

 

Scott Genzer
Senior Community Manager
RapidMiner, Inc.
Twitter Feed