late fee formula

[apgmin]

In cell A1 I put the date when the fees is supposed/committed to be paid by the student
In cell B1 I put the date when the fees is actually paid by the student

In cell C1 I put the amount which is supposed/committed to be paid by the student on date A1
In cell D1 I put the amount that is actually paid by the student
Now if he pays after the committed date or pays less than the combined amount he needs to pay a penalty which should be calculated in cell E1

The rules are as follow for computing the penalty :
If he pays less than commited then automatically 100 USD is to be paid by him
or if he pays after the comitted date then the amount should be USD 100 per month delay

the results in cell E! sould be higher of the 2 above

 

[markmzz]

thanks works perfectly

You're welcome and thanks for the feedback.

By the way, try this small modification in my last suggestion:

In E2 and copy down

=MAX((DATEDIF(A2,B2,"m")+(DAY(B2) > DAY(A2)))*100,(D2 < C2)*150)


[TABLE="class: grid, width: 392"]
<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]Date 01[/TD]
[TD]Date 02[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]12/01/2019[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]12/03/2018[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]********[/TD]
[TD]********[/TD]
[TD]********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz