One option for working with a time string is to convert it into seconds. This is useful when you haven’t got a date field, or when you need to work out how long something took rather than when it happened. You might use this option when your time measures how long it took to complete a race.
Let’s say your data is time in the form of a string, e.g. hh:mm:ss. Follow these steps to convert the string into a measure you can use.
- Right-click on the Time string and go to Transform > Custom Split. Use the colon as the delimiter and split into 3 columns.
- Rename the three new fields as Hour, Minutes and Seconds, as appropriate. For each of the three measures, click on the drop-down menu, select Change Data Type > Number (whole).
- Create a new calculated field to work out the number of seconds. You’ll need to convert each date part into seconds: multiply the minutes by 60 and the hours by 3600, then add all the parts together, as below.
And now you’ve got a calculation which gives you the time field as a measure to use in Tableau.