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

 

[par60056]

Welcome to the forum.

This can be done fairly easily. But there are lots of things that need to be considered.
1) Does the .15 cover until the end of the year regardless of when it comes in? If it is brought in 1/5/14 does the .15 cover until 12/31/14? Or is it some minimum charge IE they pay for 3 months even if it is only there for 2?
2) If it is a minimum is it until the end of the X month regardless of what day it is dropped off?
3) Is the prorating based on the number of days in the month picked up?

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

 

[ellow]

Welcome to the forum.

This can be done fairly easily. But there are lots of things that need to be considered.
1) Does the .15 cover until the end of the year regardless of when it comes in? If it is brought in 1/5/14 does the .15 cover until 12/31/14? Or is it some minimum charge IE they pay for 3 months even if it is only there for 2?
2) If it is a minimum is it until the end of the X month regardless of what day it is dropped off?
3) Is the prorating based on the number of days in the month picked up?

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

1) Ex. if a producer a brings us his product during this time (Sept 15th through Dec 31st) he will get charged flat .15 cents no matter what, if the producer decides to sell it on the last day in March his total charges would be .30 cents (.15 cents + .05 cents for 3 months = .30 cents) he only gets charged the .15 cents if he hauls it in between Sept 15th and Dec 31st, no other time will he charged a flat .15 cents

If the producer would bring us his product on Feb 1st and sell it on March 31st he would only receive .10 cents in charges ( .05 cents a month no Flat fee of .15 cents)

2) The prorating is based off the number of days in that month there product was sitting at our facility...So if there is thirty days in a month and that producer brought it in on the 1st day of the month and picked it up on the 15th he would only be charged 2 1/2 cents...Hopefully this answers all the questions. Thanks

 

[par60056]

As I said, it is a bit complicated of a formula but if you know all the conditions you can work through it. I think I did what you wanted to get to. Give this a try.

A2 is the starting date
B2 is the ending date

=if(and(a2<>"",b2<>""),IF(AND(A2>=DATE(YEAR(A2),9,15),A2<=DATE(YEAR(A2),12,31)),0.15+IF(B2>DATE(YEAR(A2)+1,1,1),((YEAR(B2)*12+MONTH(B2))-((YEAR(A2)+1)*12+1))+((B2-DATE(YEAR(B2),MONTH(B2),1))/(EOMONTH(B2,0)-DATE(YEAR(B2),MONTH(B2),1))),0)*0.05,0+(((YEAR(B2)*12+MONTH(B2))-(YEAR(A2)*12+MONTH(A2)))+((B2-DATE(YEAR(B2),MONTH(B2),1))/(EOMONTH(B2,0)-DATE(YEAR(B2),MONTH(B2),1))))*0.05),"")

 

[ellow]

Mr. Excel, Is there any other way to contact you other then through this message board? Due to complexity of what I am trying to do, I think I could explain it better to you over the phone vs. trying to type it out.

 

[par60056]

Sorry this is the way to contact me.

When you explain a problem and give some good examples people will try to help. There are lots of people that read the messages and if 1 doesn't respond another might. Sometimes there are a number of ways to do the same thing and people will provide different solutions based on the features they are familiar with.

In the end the help you get may help somebody else in the future but only if they can find it on the board.

 

[ellow]

Ok Here goes my attempt to try to explain to you what I am doing...Producers bring us a product though out different times of the year. and depending on the day they bring us the product and the day they sell It, we charge them a certain amount, called DP charges. Currently the DP charges are as follows....If the product is brought in between Sept 1st and Dec 31st, it is a flat fee of .15 cents and then .05 cents a month there after...The .15 cents up front is not pro rated, but the .05 cents per month is pro rated.

(Ex) If a producer bring us his/her product on Feb 2 and sells it on Feb 18th the product has only been here for 16 days so there for he/she will only get charged 2 3/4 cent ( he would not get charged the flat .15 cents because he brought in after Dec 31st. ) So in regards to your formula above that was great, but it was all based off of the day it was sold, not the day it was brought in, and actually has to be based off of both dates.

So in my spreadsheet that I have started there is also something called "spreads", and again the spread is based off of delivery dates and also dates the product was sold....So here is how it is laid out....Cells B3 through B8 are all the starting dates for when the product was brought in, and cells C3 through C8 are the ending dates for when the product was brought in. Cells D3 through D8 are the starting dates for when the product got sold and cells E3 through E8 is the ending date period for when that product got sold...(Ex) reading from left to right across Cell B3 = 9/1/14 Cell C3 = 11/30/14
Cell B4 = 12/1/14 Cell C4 = 2/28/15 this is the date range the producer brought the product in

and then continuing across, cell D3 = 9/1/14, cell E3 = 11/30/14
Cell D4 = 12/1/14, Cell E4 = 2/28/15 this would be the date range the producer sold his/ her product.

Cell F3 represents the spread value of .13 cents.
Cell F4 represents the spread value of .08 cents

Ok, So if a producer brought his product in on 10/15/14 and sold it on 2/28/15 this producer would get charged 25 cents in DP charges ( .15 cents plus 2 months at .05 cents totaling .10 cents) and the spread that was lost out on equates to .21 cents ( adding .13 cents because it was brought in on 10/15/14 and plus the .08 cents because it was sold on Feb 28th)

Column C, Cell C13 and below will represent what the basis was when the producer brought in his/her product (Ex.) -.20 cents, and then Column D cell D13 and below will represent what the basis was on the date he/she sold the product (Ex.) +.07 cents, Then column E, Cell E13 and below will represent where I type in the day of delivery and column F, Cell F13 and below will represent where I type in the date the producer sold his or her product. Column G cell G13 and below, will represent the amount of DP charges that the producer accumulated depending on the date he/she brought the product in (Flat fee + the .05 cents per month, or it could just be the prorated .05 cents per month, if it was brought in any other time rather then 9/1/15 through 12/31/15) and then column H is where I want it to add the basis at time of delivery Cell C13 + Basis on sale date Cell D13 plus add in the total of the spread values Cells F3 through F8 ( which that total will be based on the date the product was delivered and sold ) then Subtract Colum G Cell G13, which is the DP charges that accumulated, based on the time the product was delivered and sold..... Thanks for any help anyone can give me...complicated!!!

 

[par60056]

My formula does take into account both the starting date and the ending date. Since the original question just had 2 dates, I put starting date in A and ending date in B.

putting my formula in column 3:

2/2/2015	2/16/2015	0.02778
10/15/2014	2/28/2015	0.25
10/15/2014	11/1/2014	0.15

<tbody>
</tbody>

now you are saying that you have 2 dates for when something is brought in (columns B & C) and 2 dates for when when the product sold (columns D & E).

What is the relationship between the dates when something is brought in and the calculation? If B3=7/20/14 and C3=9/30/14 and D3=12/20/14 and E3=2/15/15, which dates do you use in the calculation? Is it B or C or the mid point between them or some prorated date based on units over time? The same applies to columns D & E.

At least telling me there are additional columns can let me alter the formula to match your data.

Here is the formula assuming that you want the time between the first drop off and the last sale (B through E)

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

 

[ellow]

1 more time.... So this is how my spreadsheet is laid out right now

Row 1 is titled "SPREAD KEY"

Column A = Spread Months
A3= Dec/Mar
A4= Mar/May
A5= May/July
A6=Jul/Sept
A7= Sep/Dec
A8= Dec/Mar

Colum B & C = (date range) Column B is the starting date of the date range & column C is the ending date range for when the product was delivered
B3= 10/1/14 C3= 11/30/14
B4= 12/1/14 C4= 2/28/15
B5= 3/1/15 C5= 4/30/15
B6= 5/1/15 C6= 6/30/15
B7= 7/1/15 C7= 8/31/15
B8= 10/1/15 C8 = 11/30/15
Column D & E = Date Range for when the product was sold Column D is the starting date and Column E is the ending date of the date range
D3= 10/1/14 E3= 11/30/14
D4=12/1/14 E4= 2/28/15
D5= 3/1/15 E5= 4/30/15
D6= 5/1/15 E6=6/30/15
D7= 7/1/15 E7=8/31/15
D8= 10/1/15 E8= 11/30/15
Column F = Spread Value
F3= 0.00 cents, this # gets manually typed in
F4= .1325 cents, this # gets manually typed in
F5= .08 cents, this # gets manually typed in
F6= .04 cents, this # gets manually typed in
F7= I don't know what this value is yet , but eventually I will enter a value in it
F8= I don't know what the value is yet, but eventually I will enter a value in it

A13 and Below = Customers name
B13 and Below = Contract #
C13 and below = Basis @ delivery....This # I will manually type in
D13 and below = Basis on sale Date....This # I will manually type in
E13 and below = Date Product was delivered....This date will be manually entered
F13 and below = Date Product was sold....This date will manually get entered
G13 and below = DP charges (this needs to be a formula)

The DP formula = if delivered any time between the dates of 10/1/14 and 12/31/14 it will automatically have a flat rate of .15 cents (Not pro rated) and then for every month that it stays on DP ( or not sold) after that date range of 10/1/14/ through 12/31/14 it will be a additional .05 cents per month which will be pro rated by the day. (Ie) if it was only on DP for 15 days, producer would only be charged 2 1/2 cents.

H13 and below = Formula that reads - Using the SPREAD KEY, if the product was brought in between the date range of Columns B through C and sold during the date range of columns D and E it would either have a spread value....(Ie) if the product was brought in during the date range Cells B3 & C3 and sold between the date range of cells D3 & E3 it would have a "0.00" value....but if the product was brought in during the date Range of Cells B3 & C3 and sold in the date Range of Cells D4 and E4 there would be a spread value of 13.25 cents....(Ie) If the product was brought in during cell range of B3 & C3 and sold in the cell range of D6 & E6 then it would have spread value of .25 cents, because you have to add all the values that are effected...you have to add all the spread values together to find out exactly how much spread was loss over that long of a period of time.

So cell H13 formula should read the total spread value ( depending on which month it was brought in and the spread value that corresponds with that date, (+) plus the month the product was sold and the spread value that corresponds to that delivery date range (+) plus any spread values in the middle...(+) plus Basis at delivery C13 and below (+) plus Basis on sale date (D13) and below (-) minus the DP charges (G13) and below.....

Even if the "Basis at delivery" is a negative, the addition needs to show up as a positive when adding it because that is a value lost out on. So if Basis at delivery is a -.20 and basis on sale date is a +.07 and the total spread value is .25 cents that needs to read as losing out on a total of .52 cents instead of reading as +.12 cents.

Then last but not least, if the formula total in H13 exceeds or is the same as the amount of DP charges we would like that cell to turn red and if it is less then the DP charges we would like those cells to turn green...Thanks for all your time, I am trying to word this right...Thanks

 

[par60056]

ok I think you now have enough explanation.

The formula I have been giving you should solve your need for Column G. It should be looking at E & F. So for G13 all references to B3 become E13 and all references to E3 become F13. Then copy the formula down column G. If it is not giving you the answer you expect, please give me the example dates and what you think it should be so I can see if there is something to adjust.

As for the Spread that is a different calculation.
1) Since over time you will continue to add to the spread table I would recommend you put it on a different worksheet so that it can continue to grow.
2) Are the dates for B&C always the same as D&E in a row?

If they are always the same you only need 1 date pair. a formula can be created to look at the dates in E & F for each row and use match to find the rows in the spread table. If the rows are the same then the value is 0 if the rows are different then the value is the sum of the spread values.