Need a formula, product being delivered for a period of time

[ellow]

Good Morning I am new to this website so I hope I am doing this right...I am looking for a formula that falls with in these guide lines....Our Rates are as follows from 9/1/14 through 12/31/14 it is a flat .15 cents and then .05 cents per month there after. The .15 cents will never be prorated but the .05 per month is prorated.
(Ex.) John brings us his product on 11/2/14 and decides to pick it up on 3/15/15 he will automatically be charged .15 cents till the end of the year, then 5 cents per month there after...that 5 cents gets prorated by the day for that month...so if his product is only up here for 15 days he would only get charged 2 1/2 cents, I would like a formula that allows me to enter the date the product was brought in and enter a date when the product was picked up, and it will spit out how much they accured in charges....I hope this makes sence....Thanks for any help you can provide

 

[ellow]

last night I was entering dates in Columns E and F which in return was effecting Column G, the DP charges...Its happening again, when I change the date in the Date Product was delivered column, it is not effecting the value in column G....For ex.

Cell E4 = 5/20/15
Cell F4 = 6/18/15

The answer that comes up in G4 = .08 cents....The real answer is that it shouldn't have accumulated more then .05 cents because it has not been there a full month....it should have been prorated to = 29 days...any thoughts?

 

[ellow]

=IF(AND(E12<>"",F12<>""),IF(AND(E12>=DATE(YEAR(E12),9,15),E12<=DATE(YEAR(E12),12,31)),0.15+IF(F12>DATE(YEAR(E12)+1,1,1),((YEAR(F12)*12+MONTH(F12))-((YEAR(E12)+1)*12+1))+((F12-DATE(YEAR(F12),MONTH(F12),1))/(EOMONTH(F12,0)-DATE(YEAR(F12),MONTH(F12),1))),0)*0.05,0+(((YEAR(F12)*12+MONTH(F12))-(YEAR(E12)*12+MONTH(E12)))+((F12-DATE(YEAR(F12),MONTH(F12),1))/(EOMONTH(F12,0)-DATE(YEAR(F12),MONTH(F12),1))))*0.05),"")

This is the formula that I am using in Column G...It seems that column E, the column that is "Date product was delivered" is all based off of the 1st day of the month, when actually it should be based off the actual day it was brought in....so for example.... if I type in a date using the 1st day of any month
(Except Sept 15th, 2014 through 12/31/14) the formula reads correct, but if I type in a date using say the 5th day of any month it doesn't change the value in column G... it assumes that everything is brought in on the 1st day of the month......(It's not prorating from the day that it was brought in) Please help 1 more time, other then this, it is working perfect....Thanks for everything that you have done.

 

[par60056]

Added prorating the first month. Using the date example above G=.0476.

=IF(AND(E13<>"",F13<>""),
IF(AND(E13>=DATE(YEAR(E13),9,15),E13<=DATE(YEAR(E13),12,31)),
0.15+IF(F13>DATE(YEAR(E13)+1,1,1),((YEAR(F13)*12+MONTH(F13))-((YEAR(E13)+1)*12+1))+((F13-DATE(YEAR(F13),MONTH(F13),1))/(EOMONTH(F13,0)-DATE(YEAR(F13),MONTH(F13),1))),0)*0.05,
(((EOMONTH(E13,0)-E13)/(EOMONTH(E13,0)-DATE(YEAR(E13),MONTH(E13),1)))+((YEAR(F13)*12+MONTH(F13))-(YEAR(E13)*12+MONTH(E13)))-1+((F13-DATE(YEAR(F13),MONTH(F13),1))/(EOMONTH(F13,0)-DATE(YEAR(F13),MONTH(F13),1))))*0.05),"")

 

[par60056]

As I said in post #2:

Working with dates and prorating can be tricky. It is important to know all the rules up front.

 

[ellow]

Mr. Excel

I was going over my excel sheet that we worked on over last week...and I think I want to change one thing if possible...I have sheet #4 laid out like this...

Cells B3 through B17 and C3 through C17 = Date Range product was brought in

B3 through B7 all have the same date in each cell, 9/1/14
B8 through B11 all have the same date in each cell, 12/1/14
B12 through B14 all have the same date in each cell, 3/1/15
B15 through B16 all have the same date in each cell, 5/1/15
B17 = 7/1/15

C3 through C7 all have the same date in each cell, 11/30/14
C8 through C11 all have the same date in each cell, 2/28/15
C12 through C14 all have the same date in each cell, 4/30/15
C15 through C16 all have the same date in each cell, 6/30/15
C17 = 8/31/15

All the cells in D3 through D17 and E3 through E17 = Date Range product was sold

D3 = 9/1/14
D4 = 12/1/14
D5 = 3/1/15
D6 = 5/1/15
D7= 7/1/15
D8 = 12/1/14
D9 = 3/1/15
D10 = 5/1/15
D11 = 7/1/15
D12 = 3/1/15
D13 = 5/1/15
D14 = 7/1/15
D15 = 5/1/15
D16 = 7/1/15
D17 = 7/1/15

E3 = 11/30/14
E4 = 2/28/15
E5 = 4/30/15
E6 = 6/30/15
E7 = 8/31/15
E8 = 2/28/15
E9 = 4/30/15
E10 = 6/30/15
E11 = 8/31/15
E12 = 4/30/15
E13 = 6/30/15
E14 = 8/31/15
E15 = 6/30/15
E16 = 8/31/15
E17 = 8/31/15

Spread Values = the following

F3 = 0
F4 = .1325
F5 = .2125
F6 = .2525
F7 = 0 For the time being, Until I know what this # will be, then I will manually type that # in here
F8 = 0 " "
F9 = .08
F10 = .12
F11 = 0...This # will manually get typed in when I know it
F12 = 0...This # will manually get typed in when I know it
F13 = .04
F14 = 0...This # will manually get typed in when I know it
F15 = 0...This # will manually get typed in when I know it
F16 = 0...This # will manually get typed in when I know it
F17 = 0...This # will manually get typed in when I know it

All these cells are listed on Sheet4, even though my main page is Sheet1, I did this so like we talked before I will be able to continue on with more dates in the future....So I still want to be able to type in what ever date the Product was delivered in Cell E3 and below on sheet 1, and type in a date Product was sold in cell F3 and below on sheet 1. Now the formula should revert back to sheet 4 and be able to follow the line across to get the spread value ( it no longer needs to be able to add up the accumulated spread value) it simply just reads across.... The formula identifies what date was typed in the column that the "Product was delivered on" sheet 1, and then matches that up with the date range on sheet 4 in columns B through C, and then it does the same for Date product was sold.....What ever date was typed in Colum F on sheet 1, the formula matches up the date range on sheet 4 Columns D & E....Then you follow that row across and that will be the spread value that gets associated with that formula, it no longer needs to add the cells up. Thanks for any help you can give me...I changed things up a little bit..Thanks

 

[ellow]

Mr. Excel...Did you get my last post?

 

[par60056]

Sorry I have work to do for my real job this week. I don't know when I will have time.