Dates and If functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a monthly spreadsheet with open items pulled forward.

I have a date in A5 which is the rental date.
A date in I5 is the due date (A5+7).
J5 is the returned date. (blank until item actually is returned)
M5 is the deposit paid for rental.
05 is the late fees

I have late fees of:
$20 if over 7 days late
$30 if over 14 days late
$50 if over 21 days late
Amount of deposit if over 30 days late

I'm trying to get a formula in 05 that will enter the appropriate late fees.
Also, if J5 is still blank after 30 days (indicating the item was not
returned), then I want to enter "N/A"into J5 on a separate row that will
automatically enter the deposit amount from M5 into 05.

Does this make sense? Or can it be done?
Thank you for helping out.
 
Hi
in O5 try:
=IF(J5="",IF(TODAY()-I5>21,50,IF(TODAY()-I5>14,30,IF(TODAY()-I5>7,20,0))))

A formula can't change the value though in cell J5 to "N/A"
 
Another pitch ..

Try in O5:

=IF(AND(TODAY()-I5>30,J5=""),M5,IF(J5="","",IF(J5-I5>30,M5,IF(J5-I5>21,50,IF
(J5-I5>14,30,IF(J5-I5>7,20,""))))))

Same caveat as Frank's:
A formula can't change the value though in cell J5 to "N/A"
 
in j5
=IF(TODAY()-I5>30,NA(),"")

Frank Kabel said:
Hi
in O5 try:
=IF(J5="",IF(TODAY()-I5>21,50,IF(TODAY()-I5>14,30,IF(TODAY()-I5>7,20,0))))

A formula can't change the value though in cell J5 to "N/A"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro or formula for dates 1
How do I change this to count days 1
match total with fees 2
@IF Formula 3
Adding cells but leaving blank if not populated 1
data from 1 sheet to the next 6
Logical functions with dates and TODAY() 9
Help with look up formula 3

Back
Top