7 Replies Latest reply on Jul 25, 2016 6:43 AM by Mark Fraser

# Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

We have a table that populates hours and minutes as literal hours and minutes (examples: "1 hour 30 mins", "6 mins", "30 hours", "2 hours 10 mins") - horrible data, I know. I need these times to show as hh:mm. I was able to create 4 additional fields to separate hours and mins into separate columns and account for null values, but am unable to find a calculation to concatenate them and turn them into 01:30, 00:06, 30:00, 02:10. I have to do this for 3 different hours/mins dimensions, so any way to simply this would also be appreciated... Workbook with formulas attached (I'm currently working in v9.3)

Currently, the formulas being used to split the columns are:

hours:

IF CONTAINS([Time Spent],"hours")=TRUE

then TRIM(SPLIT( [Time Spent], "hours", 1 ) )

ELSEIF CONTAINS([Time Spent],"hour")=true

then TRIM(SPLIT( [Time Spent], "hour", 1 ) )

ELSEIF CONTAINS([Time Spent],"h")=true

then TRIM( SPLIT( [Time Spent], "h", 1 ) )

END

mins:

IF (CONTAINS([Time Spent],"mins")=TRUE

or CONTAINS([Time Spent],"min")=TRUE)

and (CONTAINS([Time Spent],"hours")=TRUE

or CONTAINS([Time Spent],"hour")=TRUE)

then TRIM( SPLIT( [Time Spent], " ", 3 ) )

ELSEIF (CONTAINS([Time Spent],"min")=TRUE

or CONTAINS([Time Spent],"mins")=TRUE)

and (CONTAINS([Time Spent],"hours")=FALSE

or CONTAINS([Time Spent],"hour")=FALSE)

then TRIM( SPLIT( [Time Spent], " ", 1 ) )

ELSEIF CONTAINS([Time Spent],"m")=true

then TRIM( SPLIT( [Time Spent], " ", 3 ) )

END

Null Values:

Hours:

FLOAT( IIF(ISNULL([Split Time Spent Hours]),"0",[Split Time Spent Hours]))

Mins:

FLOAT(IIF(ISNULL([Split Time Spent Mins]),"0",[Split Time Spent Mins]))

Message was edited by: Faith Haynes - new attachment 7/20 @3:43pm.

• ###### 1. Re: Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

Hi Faith,

can you help with this one?

Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

Howdy Faith,

Let me just be the devil's advocate here and say is there any way what so ever that you could get this done in an ETL process or change how your data is being populated. Even just reading this in Tableau makes this field much larger and this means more work for your machine. Not much fun really.

Cheers

Carl Slifer

InterWorks

• ###### 3. Re: Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

Hi Faith

I would initially follow Carl's advice (maybe you have already, in which case apologies) to try and do something more with the output from source.

As Carl has hinted - while String manipulation is (of course) possible, it is very processor intensive and (personally) I would avoid where possible - the bigger the data set, the worst it gets.

Assuming you have done everything you can with the source, I think your on the right track...

First I would identify the hour, minute and null parts from the strings. once you have that working, look at concatenation.

You may also be able to use REG_EX, but that's for someone  smarter than me to help you with

This will help >> String Calculations in Tableau - Clearly and Simply

Quick example (I did this for fun, it should work, how well, I'm not sure, there are probably better ways...)

Minutes

>> IF CONTAINS([Time],'mins') = TRUE THEN TRIM(MID([Time],FIND([Time],"mins")-3,2)) ELSE NULL END

Hours

>> TRIM(MID(LEFT([Time],FIND([Time],"hour")-1),1,2))

minute final

>>

IF ISNULL([Minutes]) THEN '00'

ELSEIF LEN([Minutes])= 1 THEN '0'+[Minutes] ELSE [Minutes] END

Hour final

>>

IF ISNULL([Hours]) THEN '00'

ELSEIF LEN([Hours])= 1 THEN '0'+[Hours] ELSE [Hours] END

Final time

>> [hour final]+':'+[minute final]

9.3.4 copy attached

Cheers

Mark

3 of 3 people found this helpful
• ###### 4. Re: Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

We could get it done through an ETL process, however we wouldn't be able to create/add that process for another 2 months and our business partners are chomping at the bit to get this done now.

• ###### 5. Re: Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

@Mark Fraser - your solution was much nicer than my solution! Thank you so much!

Question though - how do I turn it into a true time format though? I have 2 other times, same format, that I must also turn into actual times, and then add/subtract to get time remaining or hours and minutes difference.

• ###### 6. Re: Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

Ashish Chaudhari - I added a new workbook, with packaged datasource, you should be able to open it now. Sorry for the inconvenience!

• ###### 7. Re: Convert string time (ie: 1 hour 30 mins) to time format 01:30/1:30

Hi Faith

You're very welcome!

Question though - how do I turn it into a true time format though? I have 2 other times, same format, that I must also turn into actual times, and then add/subtract to get time remaining or hours and minutes difference.

There is a function MAKETIME()

Needs hour, minute and second... so something like this (we don't have seconds, so just used 0)

MAKETIME( INT([hour final]), INT([minute final]), 0 )

You then need to deal with the formatting

What I haven't tried is then using this for time calculations... it should work, but be careful, it may give odd results (without a date part, I don't know how its going to work?! )

If I can help further, let me know

Cheers

Mark

1 of 1 people found this helpful