2 Replies Latest reply on Sep 16, 2016 10:04 AM by kettan

# Using IF THEN Statements with Strings and Dates

Hello,

I have what I believe is a fairly simple problem I'm trying to solve, yet am stuck. I need to read a database field that is currently a string and depending upon the value of the string return a time.  For example, if the field entry is "8-4" (representing an 8am-4pm shift), I want to return a value of 08:00:00 (8am).  If the field entry is "11-7", I want to return a value of 11:00:00 (11am).  Can this be done?  What is the proper syntax?  Any help would be greatly appreciated.

Thanks,
Jeff

• ###### 1. Re: Using IF THEN Statements with Strings and Dates

Hi Jeff, is it okay if the time is actually a string? Tableau doesn't have a time-only data type (that doesn't include the date).

You could try something like this to get the hour number as a string:

LEFT([Time Field], FIND([Time Field], "-")-1)

Then, you could build that string value by concatenation:

[The Calculation You Just Created] + ":00:00"

• ###### 2. Re: Using IF THEN Statements with Strings and Dates

You are much faster than I     Interestingly, we chose the same method for getting the number.  I have to say that I was tempted to do something alternative such as this:

DATETIME(INT(REPLACE([Time],"-","."))/24)

... but didn't, because I had a feeling that "-" would be more robust for other scenarios, such as hour and minutes or something like that.

I also considered DATEPARSE:

DATEPARSE("h-",[Time])

... but didn't choose it, because it is only supported for a few databases.

Here is a step-by-step explanation of a formula calculating [Time] = "8-4":

DATETIME(INT(LEFT([Time], FIND([Time],"-") - 1))/24)

 Step Understand Formula Result Formula 1. FIND([Time],"-") 2 FIND([Time],"-") 2. LEFT([Time], 2 - 1) "8" LEFT([Time], FIND([Time],"-") - 1) 3. INT("8") 8 INT(LEFT([Time], FIND([Time],"-") - 1)) 4. 8/24 0.333333333 INT(LEFT([Time], FIND([Time],"-") - 1))/24 5. DATETIME(0.333333333) 01-01-1900 08:00:00 DATETIME(INT(LEFT([Time], FIND([Time],"-") - 1))/24) 6. formatted as hh:nn 08:00