Date Difference with year and month (Formulae)

[Ombir]

Hi Friends,

I need the output as shown in column D(Expected Diff) with basic If calculation. Logic should be simple not much complicated as I will use the logic in some other tool for performance consideration. (Left, Right and Mid should be avoided if possible).

Any thoughts ?

[TABLE="width: 371"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Today[/TD]
[TD]Partition[/TD]
[TD]Current Diff(A-B)
[/TD]
[TD]Expcted Diff (A-B)
[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201809[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201808[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201807[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201806[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201805[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201804[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201803[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201802[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201801[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201712[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201711[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201710[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201709[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201708[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201707[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201706[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201705[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201704[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201703[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201702[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201701[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201612[/TD]
[TD="align: right"]197[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201611[/TD]
[TD="align: right"]198[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201610[/TD]
[TD="align: right"]199[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]201809[/TD]
[TD="align: right"]201609[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]24[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Why are you trying to avoid certain functions?? Anyway try:

=MOD(A2-B2,88)

 

[Ombir]

Thanks Steve...this is exactly I want...actually i am applying the logic in tableau and left,right and mid are creating problems in hadoop data source...This is a impala bug...you can check from below link.

https://kb.tableau.com/articles/iss...tion-output-when-connected-to-cloudera-impala

 

[steve the fish]

Just be aware this works until the difference is greater than 87 months when it will go wrong. If you are likely to have more ill have another look.

 

[Ombir]

Its fine...in my case it won't be greater than 36