How to Convert to Seconds from Data Time using Tableau or Excel
<p>I have a data CSV file where the Time Duration is expressed with a "regular" datetime syntax such as <code>12/30/99 00:00:55 AM</code>.</p>

<p>Since this value represents the time duration (and not the date and time) I need to translate it to a number that would represent the the duration in minutes such as <code>55</code> minutes.</p>

<p>I wonder if it could be possible to use Tableau's Calculated Field or Excel Expression to extract the timing <code>00:00:55</code> part and convert it to a number representing a time duration in minutes?</p>

<p>Here is how I would do this using Python:
1. First I would split the incoming value to isolate the 00:00:55 duration portion from <code>12/30/99 00:00:55 AM</code>:</p>

<pre><code>`value = '12/30/99 00:00:55 AM'`
`duration = value.split()[1]`
</code></pre>

<ol start="2">
<li>Then I would separate hours, minutes and seconds:</li>
</ol>

<p><code>hours, minutes, sec = duration.split(':')</code></p>

<ol start="3">
<li>Finally I would sum each after converting each to seconds:</li>
</ol>

<p><code>seconds = (int(hours)*60*60 + int(minutes)*60 + int(sec))</code></p>

<p>After linking CSV file Tableau goes ahead and auto assigns the data type to each column based on its own assumptions. The original CSV's <strong>Duration</strong> column field value was actually <code>00:00:00:55</code>. But since Tableau considered it as datetime type it converted in on a fly to <code>12/30/99 00:00:55 AM</code> appending <code>12/30/99</code> at the beginning and <code>AM</code> at the end. 
I right-clicked the <strong>Duration</strong> Dimension (this is how Tableau calls columns) and changed its date type to <code>String</code>. To convert this value to minutes I had to create a custom Calculated Field with following expression:</p>

<pre><code>INT(SPLIT(STR([Duration]), ':', 1))*60*60*60 + INT(SPLIT(STR([Duration]), ':', 2))*60*60 + INT(SPLIT(STR([Duration]), ':', 3))*60 + INT(SPLIT(STR([Duration]), ':', 4))/60
</code></pre>

python excel tableau