3 Replies Latest reply on Aug 14, 2018 4:22 PM by Okechukwu Ossai

    excel  formula to tableau

    Imran Azam

      Hi guys i have some formula below in excel which i need to transfer to tableau

       

      1    formula start date :  "01"&"/"&IF(MONTH($E2)<10,"0"&MONTH($E2),MONTH($E2))&"/"&YEAR($E2)

       

        

      E2start date
      16/07/201801/07/2018

      E2 is shown  above ( E2 is 16/07/2018 )the answer should be 01/07/2018 how can do this in tableau

       

      2 Formula number of months : IF(C2=B2,1,IF(DATEDIF($B2,$C2,"m")<1,1,DATEDIF($B2,$C2+15,"m")))

       

        

      B2C2number of months
      01/07/201825/07/20181

       

      B2 and C2 are shown above the output from the formula should be 1, how can this be done on tableau?

       

      thank you for any help provided

        • 1. Re: excel  formula to tableau
          marc de Latour

          what are you trying to do?

           

          get the right date?

          add a zero? it looks like your trying just to add a leading zero to your dates that are less than 1

           

           

          post sample workbook

          • 2. Re: excel  formula to tableau
            Imran Azam

            The start date formula is getting the first date of the month and year from a given date so for example if the given date is 12/08/2018 the formula will retuen 01/08/2018

            • 3. Re: excel  formula to tableau
              Okechukwu Ossai

              Hi Imran,

               

              Try this.

               

              [Start Date]

              DATETRUNC('month', [Given Date])

               

              What do you want to achieve with formula 2? It appears it is calculating datediff in months with various logic. I believe the code below should work.

               

              IF DATEDIFF('month', [Date 1], [Date 2]) < 1 THEN 1

              ELSE DATEDIFF('month', [Date 1], DATEADD('day', 15, [Date 2])) END

               

              I noticed that the cells have been locked using $ sign. So, you may use FIXED LOD expression to calculate [Given Date], [Date 1] and [Date 2]. I can't do this without seeing your data.

               

              Hope this helps.

              Ossai