Nested IF with Date Subtractions

Date Due Date Returned Fine

1 10/10/2002 10/12/2002

2 10/11/2002 10/17/2002

3 10/30/2002 11/12/2002

Fine Schedule $0.25

$1.00

if a book is returned no later than three days after due date, no fine

if a book is returned after 3 days but within 7 days after due date, fine is a quarter per day

if a book is returned after 7 days after the due date, fine is a dollar per day

Nice post. Is there supposed to be a question in there someplace?

if a book is returned no later than three days after due date, no fine if a book is returned after 3 days but within 7 days after due date, fine is a quarter per day

if a book is returned after 7 days after the due date, fine is a dollar per day

Once again, is there a question in there someplace?

Yes. I need a formula for the fine. c4 is date returned and c3 is due date It should be something like (c4-c3<= I dont know the rest but it should follow the steps below. Im suppose to find a formula that if the book is returned withing 3 days of the due date no fine if it it returned after 3 days and before 7 days the fine is a quarter per day if its after 7 days it is 1 dollar per day.

if a book is returned no later than three days after due date, no fine

if a book is returned after 3 days but within 7 days after due date, fine is a quarter per day

if a book is returned after 7 days after the due date, fine is a dollar per day

Here's the point that you have apparently been missing: Your posts included nothing but a table and a list of statements.

You didn't ask for help, you didn't say "Please", you didn't say "Thanks".

We're all volunteers here, giving freely of our own time. A post like yours comes across as "Drop what you are doing and solve my problem."

When you need help at school, work or home, do you just blurt out what you need or are you polite and ask nicely for assistance? I'll assume it's the latter and it should be no different in an on-line forum.

Ask for help, throwing in a Please and/or a Thank You and I'll bet you'll get a much better response.

I have been trying to solve this problem for a good 16 hours now with no sleep and im not exaggerating one bit. Now excuse my inappropriate way of asking for help. Now that you understand there was no disrespect towards you or anyone else who reads this I need help with this formula. If you can help me it would be greatly appreciated. Thank you.

This formula will return 0, $1.50 and $13 for the example dates you supplied. =IF(B2-A2<4, 0, IF(B2-A2<8, (B2-A2)*0.25, B2-A2))

Thank you. That is the correct formula. Now I have a question are you not suppose to use the $1 somewhere?

No need to. Since the subtraction of dates returns an integer value, any integer value multiplied by 1 will be that same value. Just format the cell as Currency to make it appear as "cash".

Now, if the fine was $0.99 or $1.01, then you need to multiply the "date differerence" by the per day fine amount, just like we did for $0.25.

Ok perfect thanks so much.

Ask Your Question

Weekly Poll