formula help please

[Imran Azam]

Hi Guys

I am trying to fix a formula that i have no idea what it is doing.

the formula is

IF(DATEVALUE(F1)-TRIM(D2)>=0,J11040,IF(DATEVALUE(F1)-TRIM(C2)>0,DATEDIF(C2,F1+30,"m")*E2,0))


below is the data

[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]
[/TR]
[TR]
[TD]1[/TD]
[TD]total[/TD]
[TD]month number[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]per month[/TD]
[TD]30/04/2016[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]54.95[/TD]
[TD]1[/TD]
[TD]01/04/2016[/TD]
[TD]30/04/2016[/TD]
[TD]54.95[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula is placed in F1 and its suppose to return some number .

Can any one help explaining what this formula is doing and why is this not working?

Thank you

 

[Leo Skywalker]

The formula does not work because if you place it in F1, it calls F1 content... so it's a circular reference. I guess it might have to be place in F2.
It seems that the formula tries to calculate how much is being charged so far.
Try this formula in F2, and from there you can see what it does... really don't see why you have TRIM with a date, unless you have day-month month-day format problems, and also DATEVALUE if dates are number, unless you are using text values.

=IF(F1>D2,"EXPIRED",IF(F1>C2,DATEDIF(C2,F1+30,"m")*E2,0))