Date Comparison with IF Formula

Redm4n83

New Member
Joined
Aug 17, 2016
Messages
19
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)
D
9$6450

<tbody>
</tbody>

Invoice Dates
(manually entered into this cell)
BCD
4INVOICE DATE18/05/2016
5DUE DATE=D4+7

<tbody>
</tbody>



Payments
( Manually Entered into Columns B and C )
( Calculated via Formula in Columns D and E )

BCDE
12DATEAMOUNTOWINGTOTAL PAID
1320/05/2016$200=D9-C13=IF(C13>1, C13, )
1424/05/2016$50=$D13-$C14=IF($C14>1, $E13+$C14, )
1501/06/2016$150$6050$400
1610/06/2016$100$5950$500
1728/07/2016$50$5900$550
1810/08/2016$250$5650$800
19
20
21
22

<tbody>
</tbody>


Fee Calculator
Calculated to give new overdue fees at a 7 day interval,

HIJKL
3DATETOTAL PAIDAMOUNT OWINGFEES DUETOTAL 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)
715/06/2016=L6-I7=J7*0.15=SUM(J7:K7)
822/06/2016=L7-I8=J8*0.15=SUM(J8:K8)
929/06/2016=L8-I9=J9*0.15=SUM(J9:K9)
106/07/2016=L9-I10=J10*0.15=SUM(J10:K10)
1113/07/2016=L10-I11=J11*0.15=SUM(J11:K11)
1220/07/2016=L11-I12=J12*0.15=SUM(J12:K12)
1327/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>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi Dan,

Take a look at this and see if it's what you're after:

Excel 2016 (Windows) 64 bit
BCDEFGHIJKL
3DATETOTAL PAIDAMOUNT OWINGFEES DUETOTAL DUE
418-5-201625-5-2016450 6,000,00 900,00 6,900,00
525-5-20161-6-2016650 6,250,00 937,50 7,187,50
68-6-2016400 6,787,50 1,018,13 7,805,63
7
8
96450
10
11
12DateAmountOwingtotal paid
1320-5-20162006250200
1424-5-2016506200250
151-6-20161506050400
1610-6-20161005950500
1728-7-2016505900550
1810-8-20162505650800

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I4=SUMIF($B$13:$B$18,"<="&H4,$E$13:$E$18)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0
Hello Joris

Thank you very much, With a few minor modifications i am able to use it throughout my whole sheet.
Code:
=SUMIF(B13:B22,"<"&H4,C13:C22)

Now i can safely start harassing bad debtors and with any luck they may start paying me.

Thanks again mate.
:)
 
Upvote 0
After careful reading your post again; this might be closer to my earlier respons; this one is including an alternative for the SUMIF part as well.

Excel 2016 (Windows) 64 bit
BCDEFGHIJKLMN
3DATETOTAL PAIDAMOUNT OWINGFEES DUETOTAL DUETOTAL PAID
418-5-201625-5-2016250 6,200,00 930,00 7,130,00 250
525-5-20161-6-2016400 6,730,00 1,009,50 7,739,50 400
68-6-2016400 7,339,50 1,100,93 8,440,43 400
715-6-2016500 7,940,43 1,191,06 9,131,49 500
822-6-2016500 8,631,49 1,294,72 9,926,21 500
9645029-6-2016500 9,426,21 1,413,93 10,840,14 500
10
11
12DateAmountOwingtotal paid
1320-5-20162006250200
1424-5-2016506200250
151-6-20161506050400
1610-6-20161005950500
1728-7-2016505900550
1810-8-20162505650800

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I4=SUMIF($B$13:$B$18,"<="&H4,$C$13:$C$18)
N4=INDEX($E$13:$E$18,MATCH(H4,$B$13:$B$18,1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top