CODEWITHSUNDEEP
pythonexceltableau-api
alphanumeric
alphanumeric

Reputation: 19359

How to Convert to Seconds from Data Time using Tableau or Excel

I have a data CSV file where the Time Duration is expressed with a "regular" datetime syntax such as 12/30/99 00:00:55 AM.

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 55 minutes.

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

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 12/30/99 00:00:55 AM:

`value = '12/30/99 00:00:55 AM'`
`duration = value.split()[1]`
  1. Then I would separate hours, minutes and seconds:

hours, minutes, sec = duration.split(':')

  1. Finally I would sum each after converting each to seconds:

seconds = (int(hours)*60*60 + int(minutes)*60 + int(sec))

Upvotes: 2

Views: 1046

Answers (3)

alphanumeric
alphanumeric

Reputation: 19359

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 Duration column field value was actually 00:00:00:55. But since Tableau considered it as datetime type it converted in on a fly to 12/30/99 00:00:55 AM appending 12/30/99 at the beginning and AM at the end. I right-clicked the Duration Dimension (this is how Tableau calls columns) and changed its date type to String. To convert this value to minutes I had to create a custom Calculated Field with following expression:

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

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34325

If you wanted to do it just in an Excel formula to get to seconds would be similar to Python:-

=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)

assuming the value can be read from the CSV file into an Excel datetime value in cell A1.

Another way of doing it (using the fact that time values in Excel are fractions of a day) is

=MOD(A1,1)*24*3600

Upvotes: 1

EAS_W
EAS_W

Reputation: 43

This would be a very crude way of doing it and would rely on the format being the same.

=(MID(E3,10,2)×60×60)+(MID(E3,13,2)×60)+(MID(E3,16,2))

MID - (Cell Reference, Start Position, No. of Characters to Extract)

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.

Upvotes: 0

Related Questions