Dates and If functions

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

 

[Guest]

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"

 

[Max]

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"

 

[Guest]

Thank you both for helping me out. You're great!

 

[Max]

Thank you both for helping me out. You're great!

You're welcome !
Thanks for posting back

 

[Guest]

in j5
=IF(TODAY()-I5>30,NA(),"")

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"