help understanding formula

[Imran Azam]

Hi Guys

can anyone explain to me what this formula is doing?

the formula: =IF(AND($C2=1,G2<>0),0,IF(AND($C2=1,MONTH($B2)&YEAR($B2)=MONTH(H$1)&YEAR(H$1)),$F2,IF(MONTH($A2)&YEAR($A2)=MONTH(H$1)&YEAR(H$1),$F2,IF(AND(RIGHT(H$1,4)&MID(H$1,4,2)&LEFT(H$1,2)>TEXT($D2,"YYYYMMDD"),RIGHT(H$1,4)&MID(H$1,4,2)&LEFT(H$1,2)<= TEXT($E2,"YYYYMMDD")),$F2,0))))

data below
[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]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start Date[/TD]
[TD]END DATE[/TD]
[TD]LENTH[/TD]
[TD]first date of Start[/TD]
[TD]first date of END[/TD]
[TD] per mth[/TD]
[TD]30/06/2016[/TD]
[TD]31/07/2016[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]13/08/2015[/TD]
[TD]12/08/2016[/TD]
[TD]12[/TD]
[TD]01/08/2015[/TD]
[TD]31/07/2016[/TD]
[TD]133.33[/TD]
[TD]1466.63[/TD]
[TD]133[/TD]
[/TR]
</tbody>[/TABLE]

the formula is typed in column H2 ( the output is 133 )

thank you for any help provided.

 

[Special-K99]

The formula returns the value in F2 if one of these conditions is TRUE

C2=1 and G2 <> 0
or
C2 =1 and the year and month of B2 match the year and month of H1
or
year and month of A2 match the year and month of H1
or
The date in H1 is greater than the date in D2 and the date in H1 is less than or equal to the date in E2

otherwise return 0

 

[wideboydixon]

Let's break it down:

IF(AND($C2=1,G2<>0),0

If LENTH is 1 and the value under 30/06/2016 is not 0 then the value is 0.

IF(AND($C2=1,MONTH($B2)&YEAR($B2)=MONTH(H$1)&YEAR(H$1)),$F2

If LENTH is 1 and the END DATE month/year is the same as H1 (July 2016) then the value is the same as F2

IF(MONTH($A2)&YEAR($A2)=MONTH(H$1)&YEAR(H$1),$F2

If the Start Date month/year is the same as H1 (July 2016) then the value is the same as F2

IF(AND(RIGHT(H$1,4)&MID(H$1,4,2)&LEFT(H$1,2)>TEXT($D2,"YYYYMMDD"),RIGHT(H$1,4)&MID(H$1,4,2)&LEFT(H$1,2)<= TEXT($E2,"YYYYMMDD")),$F2

This is a complex way to say if H1 is after first date of Start and H1 is before first date of END then the value is same as F2 - this is the condition that's met in your example. If none of the conditions above are met then the value is 0.

I assume H2 is formatted without decimal places as the actual value should be 133.33. H1 must also be formatted as text and not an actual date. It may well simplify to this:

=IF(AND($C2=1,G2<>0),0,IF(AND($C2=1,EOMONTH($B2,0)=EOMONTH(H$1,0)),$F2,IF(EOMONTH($A2,0)=EOMONTH(H$1,0),$F2,IF(AND(DATEVALUE(H$1)>$D2,DATEVALUE(H$1)<=$E2),$F2,0))))

WBD

 

[Special-K99]

Let's break it down:

IF(AND(RIGHT(H$1,4)&MID(H$1,4,2)&LEFT(H$1,2)>TEXT($D2,"YYYYMMDD"),RIGHT(H$1,4)&MID(H$1,4,2)&LEFT(H$1,2)<= TEXT($E2,"YYYYMMDD")),$F2

This is a complex way to say if H1 is after first date of Start and H1 is before or equal to the first date of END then the value is same as F2 - this is the condition that's met in your example. If none of the conditions above are met then the value is 0.

I assume H2 is formatted without decimal places as the actual value should be 133.33. H1 must also be formatted as text and not an actual date. It may well simplify to this:

=IF(AND($C2=1,G2<>0),0,IF(AND($C2=1,EOMONTH($B2,0)=EOMONTH(H$1,0)),$F2,IF(EOMONTH($A2,0)=EOMONTH(H$1,0),$F2,IF(AND(DATEVALUE(H$1)>$D2,DATEVALUE(H$1)<=$E2),$F2,0))))

WBD

See above