I'm trying to solve my issue in excel and php, i want to add fees every 5 months if customer delay to pay his invoice..

here is the formula example.. we have 3 tranche in every year..

**Date1**: 1/1/2020

**date2**: 2/10/2021

now, every 5 months we add 15% fees, normal price is 100$.

`t1 = 100 + (100 * 15%) + (100 * 9 * 0.5%) = `

t2 = 100 + (100 * 15%) + (100 * 6 * 0.5%) =

t3 = 100 + (100 * 15%) + (100 * 3 * 0.5%) =

t4 = 100 + (100 * 15%) + (100 * 0 * 0.5%) =

the problem is how to calculate to get the number of month **9,6,3,0** between the two date...

`$date1 = "2020-01-01";`

$date2 = "2021-02-10";

$diff = abs(strtotime($date2) - strtotime($date1));

$years = floor($diff / (365*60*60*24));

$months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));

i want to get the number of month and check if it's more than 5 month, we add 15% else continue... i think i need to have for loop (loop 4 times since we have 4 tranche), please if someone can help, i tried many times but not working...

if someone can help me in excel or php , if i get one logic, i will transform code not problem..

thanks in advance..

**UPDATED**

What I mean by 9,6,3,0

here is the example.

Suppose we have two dates: **1/1/2020** - **2/10/2021**

the invoice should be paid in **4/1/2020** if not, we add 15% fees in the next month **5/1/2020**.

Now, if we count from **6/1/2020** to **2/10/2021** so, the difference between the last dates is **9**

again, we calculate with the same way, we will get 6, then again 3, then again 0, so it's 4 tranche...

I think if we get the first number 9, we can always minus by 3.

`9-3 = 6`

6-3 = 3

3-3 = 0

hope it's clear now!

**UPDATED 2**

1/1/2020 - 10-02-2021

`Months:`

1-2-3 4-5-6 7-8-9 10-11-12

1 2 3 4

month 5 = 15%

count from month 6 to month 2

result 9;

4 5 6 7

Month 8 = 15%

count from month 9 to month 2

result 6;

7-8-9-10

Month 11 = 15%

count from month 12 to month 2

result 3;

10-11-12-01

Month 02 =15%

count from month 3 to month 2 (not

possible since date end in 10-02-2021

result 0;

**Total is 9+6+3+0= 18**

So, the formula will be:

$total_to_pay = 100 + (100 * 15%) + (100 * 18 * 0.5%);

Hope now it's clear..

My Answer:

In Excel, you can use the following formula to calculate the number of months between two dates and then determine the fees to be added based on the tranches:

Assuming Date1 is in cell A1 and Date2 is in cell B1, you can use the following formula in Excel:

`excel`

=IF(MONTH(B1)-MONTH(A1)>=5, 15%, 0)

This formula will check if the difference in months between Date1 and Date2 is greater than or equal to 5. If it is, it will return 15%, otherwise it will return 0.

You can then use this logic to calculate the total fees to be added based on the tranches.

In PHP, you can use the following code to calculate the number of months between two dates and determine the fees to be added based on the tranches:

`php`

$date1 = "2020-01-01";

$date2 = "2021-02-10";

$diff = abs(strtotime($date2) - strtotime($date1));

$months = floor($diff / (30*24*60*60));

$tranches = floor($months / 5);

$total_fees = 0;

for ($i = 0; $i < $tranches; $i++) {

$total_fees += 15;

}

$total_to_pay = 100 + (100 * 15/100) + (100 * $total_fees * 0.5/100);

echo $total_to_pay;

This PHP code will calculate the number of months between Date1 and Date2, determine the number of tranches based on 5 months intervals, calculate the total fees to be added, and then calculate the total amount to be paid with the fees included.

## Rate this post

3 of 5 based on 9600 votes## Comments