late fee charges

[nasir ahmad]

i have a worksheet of daily fee collection with this formula check on classess

=IF(E5="Play Group",3940, IF(E5="Nursery",3980, IF(E5="Kindergarten",4020)))

(where as 3940,3980 and 4020 is fee)

i want to calculate the late fee amount of 15 per day after each a specific due date like 15/04/08.

late fee is fixed i.e. 15 which will increase per day like 15 for 16/04/08 and onwords

like on 16/04/08 3940 become 3955

how i can use IF formula with dates. like if date is > greater than 15 of each month plus 15 with normal ammount.

help is required on urgent basis.

 

[Pete_UK]

If you have an urgent request, then it will help if you give details of the
sheet layout etc so we know where your dates are. Do you have a column for
Date Due, and another column for Date Paid? If so, then you can just
subtract one from the other to give you the number of days difference and
then multiply this by 15 and add it on to the fee. You may choose to do this
in separate columns, or combine everything into one complex formula.

Hope this helps.

Pete

 

[timmg]

First, I recomend that you switch from the deeply nested if statements
to a vlookup or hlookup. That will make maintenance a whole lot easier
- you can more easily see the fees for play group, etc. Assuming
column A as the activity type you can place the fee in column B and
the late fee in column C.

Now I'm a little confused on how you're triggering the late fee. The
formula =today() - E5 will yield the number of days between today's
date and the date value in cell E5.

You might want to have a fee column and a fee plus penalty column that
displays where the the penalty cell uses the fee cell and a lookup to
find the penalty once the days threshold has been met. Assuming the
comparision date in B5 you could do something like =if(today()=B5>15,
copyfee + lookuppentalty, 0) where copyfee is the location of the fee
and lookuppentalty is another lookup)

lemme know if you need more help on lookup expressions

hth,

Tim Mills-Groninger