Working With Time Strings: Part I - Converting to DATETIME

by Jeremy Kneebone

I find some time formats really annoying to work with. I had particular trouble recently when using time data in the format <hour: minute: string>, so I thought I’d share how I converted that into a time format that Tableau could work with.

 

Tableau can’t read time on its own. You’ve got two options: you can either convert the time into seconds and use it as a measure, or combine it with a date field to work with date and time together. The first is useful for working out how long something takes, the second is useful for logging the exact moment events occurred.

 

In my data I had a Date string, which I simply converted into a date. However, I also had a Time string, which I couldn’t just convert into a time, because Tableau doesn’t read times on their own. Here’s what I did.

 

  1. Right-click on the Time string and go to Transform > Custom Split. Use the colon as the delimiter and split into 3 columns.
  2. 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).
  3. You now need to combine those into a Date and Time format, so that you can combine it with the Date later. Create a new calculation with the MAKETIME function (see below). It combines your Hour, Minute and Second fields.

  1. Now combine your new date and time fields into a Date and Time field. Create a calculation using the MAKEDATETIME function, as below. Drag your Date field and Time (to use) calculation into the calculation.