1 Reply Latest reply on Oct 31, 2018 4:30 PM by Shinichiro Murakami

Help With Calculation

Hello Guys

i am a little bit stuck with few calculation, unable to find a proper solution.

PFA excel sheet and Tableau Workbook.(2018.2.2)

Need to write calculation for below mentioned fields in Excel

1) A in Months ( Column X in Excel)--->   =IF(O2="G",(S2-K2)/30,X3) ( i was able to get this calculation but in excel they are showing in decimals but iam not getting in decimals)

2) A in Days (Column Y in Excel)------->   =IF(O2="G",S2-K2,Y3)         (This i was able to achieve)

3) Time Days Column AA in Excel)---->

=IF(AND(\$M2="NO",\$O2="A",\$S2<>"#"),MAX(0,\$S2-\$K2),IF(AND(\$M2="NO",\$O2="A",\$R2<>"#"),MAX(0,\$R2-\$K2),IF(AND(\$M2="NO",\$O2="A",\$Q2<>"#"),MAX(0,\$Q2-\$K2),IF(AND(\$M2="NO",\$O2="A",\$P2<>"#"),MAX(0,\$P2-\$K2),IF(AND(\$M2="YES",\$O2="A"),\$B\$2-\$K2,IF(AND(\$M2="NO",\$O2="A",\$P2="#",\$Q2="#",\$R2="#",\$S2="#"),"No completion date recorded","N/A"))))))

4) Status 2 (Column AB in Excel)----->  =IF(AND(AA2<>"N/A",AA2<>"No completion date recorded"),IF(AA2>Y2,"MISS","HIT"),"N/A")

5) Landing Date (Column AC in Excel)-----> =IF(AND(\$M2="NO",\$O2="A"),IF(\$S2<>"#",\$S2,IF(\$R2<>"#",\$R2,IF(\$Q2<>"#",\$Q2,IF(\$P2<>"#",\$P2,\$B\$3)))),IF(AND(\$M2="YES",\$O2="A"),\$S3,\$B\$3))

Thank You any help is appreciated.

Regards

Johnny.