IF Formula Help

New Contributor

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

3 Replies
best response confirmed by Hans Vogelaar (MVP)



You can try this formula.


thank you so much. looks like it worked

@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.