IF Formula Help

[blessedmommy77]

What IF formula should I use for: if an amount is paid after the 10th of the month add 5%? My spreadsheet is set up with Amount Paid in 3H, Date Paid in 3I, and Balance Due in 3J

[#2 · Accepted solution]

@blessedmommy77 

=IF(DAY(I3)>10,H3*1.05,H3)

You can try this formula.

[#3]

thank you so much. looks like it worked

[#4]

@blessedmommy77 Caution: When using DAY(I3), you should consider what happens if payment was delayed until the following month (or later).  You probably don't want that 5% to be avoided if that happens.

So it depends on what you mean by "the month".  If, e.g., you mean "the month of the Due Date", a better formula would be (assuming Due Date is in column D):

=IF( I3>DATEVALUE(YEAR(D3)&"-"&MONTH(D3)&"-10"), H3*1.05, H3 )

...although...  Calculating the Balance Due as a factor times Amount Paid does not seem right, either.  Shouldn't Balance Due be Prior Balance plus a factor times Amount Billed (or whatever name you use) for some month?  Or maybe a factor times the sum of Prior Balance plus Amount Billed?  Or maybe instead of Balance Due you just mean Charge For This Month.