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.

	A	B	C	D	E	F	G	H	I	J	K
1	NAME	START DATE	END DATE	TOTAL	LENGTH OF CONTRACT (NO OF MONTHS)	FIRST DATE OF START DATE	LAST DATE OF END DATE	PER MONTH	30/06/2016	31/07/2016	31/08/2016
2	subscription 1	07/04/2016	07/04/2016	95	1	01/04/2016	30/04/2016	95.00		-	-
3	subscription 2	03/04/2016	09/08/2016	479.16	5	01/04/2016	31/08/2016	95.83	95.83	96	96

<tbody>
</tbody>

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?