formula understanding and improving

[Imran Azam]

Hi Guys

hope all is well

i have the below table at work with data and forumla, and i need help understanding

1) what the formula are doing
2) if i can improve these formula ( is there a more efficient way to do this.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NAME[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]TOTAL[/TD]
[TD]LENGTH OF CONTRACT (NO OF MONTHS)[/TD]
[TD]FIRST DATE OF START DATE[/TD]
[TD]LAST DATE OF END DATE[/TD]
[TD] PER MONTH[/TD]
[TD]30/06/2016[/TD]
[TD]31/07/2016[/TD]
[TD]31/08/2016[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]subscription 1[/TD]
[TD]07/04/2016[/TD]
[TD]07/04/2016[/TD]
[TD]95[/TD]
[TD]1[/TD]
[TD]01/04/2016[/TD]
[TD]30/04/2016[/TD]
[TD]95.00[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]subscription 2[/TD]
[TD]03/04/2016[/TD]
[TD]09/08/2016[/TD]
[TD]479.16[/TD]
[TD]5[/TD]
[TD]01/04/2016[/TD]
[TD]31/08/2016[/TD]
[TD]95.83[/TD]
[TD]95.83[/TD]
[TD]96[/TD]
[TD]96[/TD]
[/TR]
</tbody>[/TABLE]


the formula for subscription 1 cell I2,J2,K2 are as follows

cell I2 : IF(DATEVALUE(I$1)-TRIM($G2)>=0,$D$2,IF(DATEVALUE(I$1)-TRIM($F2)>0,DATEDIF($F2,I$1+30,"m")*H2,0))

cell J2 : below IF(AND($E2=1,I2<>0),0,IF(AND($E2=1,MONTH($C2)&YEAR($C2)=MONTH(J$1)&YEAR(J$1)),$H2,IF(MONTH($B2)&YEAR($B2)=MONTH(J$1)&YEAR(J$1),$H2,IF(AND(RIGHT(J$1,4)&MID(J$1,4,2)&LEFT(J$1,2)>TEXT($F2,"YYYYMMDD"),RIGHT(J$1,4)&MID(J$1,4,2)&LEFT(J$1,2)<= TEXT($G2,"YYYYMMDD")),$H2,0))))

cell K2 : below : IF(AND($E2=1,J2<>0),0,IF(AND($E2=1,MONTH($C2)&YEAR($C2)=MONTH(K$1)&YEAR(K$1)),$H2,IF(MONTH($B2)&YEAR($B2)=MONTH(K$1)&YEAR(K$1),$H2,IF(AND(RIGHT(K$1,4)&MID(K$1,4,2)&LEFT(K$1,2)>TEXT($F2,"YYYYMMDD"),RIGHT(K$1,4)&MID(K$1,4,2)&LEFT(K$1,2)<= TEXT($G2,"YYYYMMDD")),$H2,0))))

the formula for subscription 2 cell I3,J3,K3 are as follows

cell I3: IF(DATEVALUE(I$1)-TRIM($G3)>=0,D3,IF(DATEVALUE(I$1)-TRIM($F3)>0,DATEDIF($F3,I$1+30,"m")*H3,0))

cell J3: below
IF(AND($E3=1,I3<>0),0,IF(AND($E3=1,MONTH($C3)&YEAR($C3)=MONTH(J$1)&YEAR(J$1)),$H3,IF(MONTH($B3)&YEAR($B3)=MONTH(J$1)&YEAR(J$1),$H3,IF(AND(RIGHT(J$1,4)&MID(J$1,4,2)&LEFT(J$1,2)>TEXT($F3,"YYYYMMDD"),RIGHT(J$1,4)&MID(J$1,4,2)&LEFT(J$1,2)<= TEXT($G3,"YYYYMMDD")),$H3,0))))

cell K3: below
=IF(AND($E3=1,J3<>0),0,IF(AND($E3=1,MONTH($C3)&YEAR($C3)=MONTH(K$1)&YEAR(K$1)),$H3,IF(MONTH($B3)&YEAR($B3)=MONTH(K$1)&YEAR(K$1),$H3,IF(AND(RIGHT(K$1,4)&MID(K$1,4,2)&LEFT(K$1,2)>TEXT($F3,"YYYYMMDD"),RIGHT(K$1,4)&MID(K$1,4,2)&LEFT(K$1,2)<= TEXT($G3,"YYYYMMDD")),$H3,0))))

can anyone help with this?

Thank you

 

[Imran Azam]

anyone?