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>This would be a very crude way of doing it and would rely on the format being the same.</p>

<pre><code>=(MID(E3,10,2)Ã60Ã60)+(MID(E3,13,2)Ã60)+(MID(E3,16,2))
</code></pre>

<p>MID - (Cell Reference, Start Position, No. of Characters to Extract)</p>

<p>This assumes the value is in E3, this should be changed to what ever cell you have the original value of "12/30/99 00:00:55 AM" in.</p>

python excel tableau