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

      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


      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.