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

    Using IF THEN Statements with Strings and Dates

    info consulting

      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
          David Li

          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
            kettan

            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)

             

            StepUnderstand FormulaResultFormula
            1.FIND([Time],"-")2FIND([Time],"-")
            2.LEFT([Time], 2 - 1)"8"LEFT([Time], FIND([Time],"-") - 1)
            3.INT("8")8INT(LEFT([Time], FIND([Time],"-") - 1))
            4.8/240.333333333INT(LEFT([Time], FIND([Time],"-") - 1))/24
            5.DATETIME(0.33333333301-01-1900 08:00:00  DATETIME(INT(LEFT([Time], FIND([Time],"-") - 1))/24)
            6.formatted as hh:nn08:00