Good afternoon all.
I am chasing a little assistance with how i would use the IF Formula to compare 2 dates.
Ive done numerous research and have resulted in having to ask someone here.
For ease of understanding i have just put the some code in the appropriate cells instead of the results.
Total Moneys owing
(manually entered into this cell)
<tbody>
</tbody>
Invoice Dates
(manually entered into this cell)
<tbody>
</tbody>
Payments
( Manually Entered into Columns B and C )
( Calculated via Formula in Columns D and E )
<tbody>
</tbody>
Fee Calculator
Calculated to give new overdue fees at a 7 day interval,
<tbody>
</tbody>
What i am trying to do is use the following code
What is required is that The over Due Fee Calculator will automatically create and re-adjust what fees are due taking into consideration to the time at which payments are made.
As there is a 15% overdue fee that is calculated on current owings, these owings can change as part payments are made.
The issues i have been having - I am unable to retrieve a result in the Total Paid Column.
I understand that this is quite easy to manually enter into the table, But the aim is to have it automatically calculate and enter the correct figures into the correct places.
Thanks in advance, I hope i have created enough information here for you to work with, Unfortunately i am unable to upload anything for your reference.
Regards
Dan
<tbody></tbody>
I am chasing a little assistance with how i would use the IF Formula to compare 2 dates.
Ive done numerous research and have resulted in having to ask someone here.
For ease of understanding i have just put the some code in the appropriate cells instead of the results.
Total Moneys owing
(manually entered into this cell)
D | |
9 | $6450 |
<tbody>
</tbody>
Invoice Dates
(manually entered into this cell)
B | C | D | |
4 | INVOICE DATE | 18/05/2016 | |
5 | DUE DATE | =D4+7 |
<tbody>
</tbody>
Payments
( Manually Entered into Columns B and C )
( Calculated via Formula in Columns D and E )
B | C | D | E | |
12 | DATE | AMOUNT | OWING | TOTAL PAID |
13 | 20/05/2016 | $200 | =D9-C13 | =IF(C13>1, C13, ) |
14 | 24/05/2016 | $50 | =$D13-$C14 | =IF($C14>1, $E13+$C14, ) |
15 | 01/06/2016 | $150 | $6050 | $400 |
16 | 10/06/2016 | $100 | $5950 | $500 |
17 | 28/07/2016 | $50 | $5900 | $550 |
18 | 10/08/2016 | $250 | $5650 | $800 |
19 | ||||
20 | ||||
21 | ||||
22 |
<tbody>
</tbody>
Fee Calculator
Calculated to give new overdue fees at a 7 day interval,
H | I | J | K | L | |
3 | DATE | TOTAL PAID | AMOUNT OWING | FEES DUE | TOTAL DUE |
4 | =D5 | =D9-I4 | =J4*0.15 | =SUM(J4:K4) | |
5 | =H4+7 | =L4-I5 | =J5*0.15 | =SUM(J5:K5) | |
6 | =H5+7 | =L5-I6 | =J6*0.15 | =SUM(J6:K6) | |
7 | 15/06/2016 | =L6-I7 | =J7*0.15 | =SUM(J7:K7) | |
8 | 22/06/2016 | =L7-I8 | =J8*0.15 | =SUM(J8:K8) | |
9 | 29/06/2016 | =L8-I9 | =J9*0.15 | =SUM(J9:K9) | |
10 | 6/07/2016 | =L9-I10 | =J10*0.15 | =SUM(J10:K10) | |
11 | 13/07/2016 | =L10-I11 | =J11*0.15 | =SUM(J11:K11) | |
12 | 20/07/2016 | =L11-I12 | =J12*0.15 | =SUM(J12:K12) | |
13 | 27/07/2016 | =L12-I13 | =J13*0.15 | =SUM(J13:K13) |
<tbody>
</tbody>
What i am trying to do is use the following code
Code:
=IF(H4>=B22, E22, IF(H4>=B21, E21, IF(H4>=B20, E20, IF(H4>=B19, E19, IF(H4>=B18, E18, IF(H4>=B17, E17, IF(H4>=B16, E16, IF(H4>=B15, E15, IF(H4>=B14, E14, IF(H4>=B13, E13,0 ))))))))))
What is required is that The over Due Fee Calculator will automatically create and re-adjust what fees are due taking into consideration to the time at which payments are made.
As there is a 15% overdue fee that is calculated on current owings, these owings can change as part payments are made.
The issues i have been having - I am unable to retrieve a result in the Total Paid Column.
I understand that this is quite easy to manually enter into the table, But the aim is to have it automatically calculate and enter the correct figures into the correct places.
Thanks in advance, I hope i have created enough information here for you to work with, Unfortunately i am unable to upload anything for your reference.
Regards
Dan
<tbody></tbody>