-1

I want to calculate an interest on a fee, that is if a client has not paid full amount of fee before the 15th of the month an interest of 10% should added to the amount to be paid

my cells are Amount payable Column B Amount paid Column c upstanding amount Column D Date for payment Column E penalty to be Paid 10% Column

How do i calculate this ?

i tried the below formula and had Err:509 

=IF(E2=TODAY()>15, "Overdue" "B2*10/100",)

what i was trying to accomplish is .If today is on the 15th or the date is pass 15th of the month and there is upstanding amount to be paid attract a penalty of 10%

enter image description here

lord-ivan
  • 73
  • 2
  • 3
  • 12

1 Answers1

0

Things you could try 1) =if (e2>15, "overdue", b2*.10) or =if (text (now (),"dd") > 15, "overdue", b2*.1) You don't have a true and a false statement here (or rather the false statement is blank), also I don't know what you're trying to accomplish with e2=today ()>15 - if you're trying to get the day of the current month, you'll need e2=day (today ())>15

bdpolinsky
  • 341
  • 5
  • 18
  • what i was trying to accomplish with the e2=today ()>15 is .If today is on the 15th and there is upstanding amount to be paid attract a penalty of 10% – lord-ivan Aug 20 '16 at 13:06
  • https://support.office.com/en-us/article/TODAY-function-5eb3078d-a82c-4736-8930-2f51a028fdd9 – bdpolinsky Aug 20 '16 at 13:13
  • Today returns the current date in mm/dd/yeyy format. You need to specify the day part of that. What your formula does now is =if (e2=8/20/2016>15, "overdue", b2*.1) – bdpolinsky Aug 20 '16 at 13:15
  • Also how does your formula check if it's been paid? All you're doing is checki ng if the day is greater than 15. Let's say someone pays the interest on the 14th. Once the 15th rolls around, your formula will say that they're overdue. – bdpolinsky Aug 20 '16 at 13:17
  • So what is the Correct formula for this – lord-ivan Aug 20 '16 at 14:37
  • Without seeing your data and how its set up I can't definitely answer that question. – bdpolinsky Aug 20 '16 at 14:50
  • I have updated the question with a sample data picture – lord-ivan Aug 20 '16 at 14:59
  • The issue is you don't have a column that tracks when the payment is made. The today () function tracks today's date - so if you used that formula, and accessed the spreadsheet today, it would show today's date - if you accessed it tomorrow it would show tomorrow's date. – bdpolinsky Aug 20 '16 at 15:04
  • So you need to add a column next to amount paid for "date of payment". Let's say it's in d and you're examining f. Then your formula is something like =if (day (d1) >= 15, b1*.1, 0. – bdpolinsky Aug 20 '16 at 15:07
  • Also, if you're using Libreoffice, please don't tag this under excel. – bdpolinsky Aug 20 '16 at 15:07
  • Wow it works i tired this formula (=IF (DAY (E2) >= 15,AND(D2>=1.00), B2*0.1, 0)) check if it's been paid? and i get Err:504 – lord-ivan Aug 20 '16 at 15:31
  • No comma after 15 and it needs to be and if not just and – bdpolinsky Aug 20 '16 at 15:32
  • Error 504 reads - Function parameter is not valid, for example, text instead of a number, or a domain reference instead of cell reference. -- firstly this is why you can't tag something under excel if you're using Libreoffice because the functions are different. You've changed it and added Libreoffice but you've kept the excel tags and this is not an excel post. Secondly, check to make sure your e column is type date and your dad column is type number. You don't need the GHS in it- try it with just the 200 - it might be throwing off your formula as it turns it into a string rather than a numb – bdpolinsky Aug 20 '16 at 15:39
  • If there's an answer is customary to mark it as "answered" that way other users who find this post can have their question answered. – bdpolinsky Aug 20 '16 at 15:51