Nested IF with Date Subtractions

October 26, 2010 at 06:12:03
Specs: Windows Vista
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

See More: Nested IF with Date Subtractions

Report •

#1
October 26, 2010 at 06:37:39
Nice post.

Is there supposed to be a question in there someplace?


Report •

#2
October 26, 2010 at 06:38:29
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


Report •

#3
October 26, 2010 at 07:41:52
Once again, is there a question in there someplace?


Report •

Related Solutions

#4
October 26, 2010 at 08:05:12
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


Report •

#5
October 26, 2010 at 08:25:20
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.


Report •

#6
October 26, 2010 at 08:36:00
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.

Report •

#7
October 26, 2010 at 09:01:09
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))


Report •

#8
October 26, 2010 at 11:23:26
Thank you. That is the correct formula. Now I have a question are you not suppose to use the $1 somewhere?

Report •

#9
October 26, 2010 at 11:41:39
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.


Report •

#10
October 26, 2010 at 12:06:32
Ok perfect thanks so much.

Report •

Ask Question