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

# excel  formula to tableau

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)

 E2 start date 16/07/2018 01/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")))

 B2 C2 number of months 01/07/2018 25/07/2018 1

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

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

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

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