First off, Roger's formula is much better.

=MAX(0,K2-6)*17+MAX(0,K2-10)*33+MAX(0,K2-30)*50

I had trouble with at first trying to figure out where 33 and 50 came from.
It's really ingenous though. 33 is 50-17, and 50 is 100-33-17. Days 11-30
are really being multiplied like x*17+x*33 (same as x*50).

Second, your current question. You describe it differently than before, but
I assume you meant.

1-6 days results in 0
7 days results in 30
13 days results in 210
14 days results in 290

If so then you would use
=MAX(0,K2-6)*30+MAX(0,K2-13)*50

or did you mean this?
1-6 days results in 0?
7 days results in 210? (it sounded like you wanted to charge 30 a day)
8 days results in 290?

if so you would use
=if(K26,K2*30+MAX(0,K2-7)*50,0)

Let me know if you need more help.

"Bryan J Bloom" wrote:

absolutely perfect! thank you very much. Last question. If I wanted to change
the values (17,50 and 100), would I have to calculate the maximums of the
first two values again and just input that in the middle of the formula and
change the values in the formula? Or am I way off? I have to make this
formula work (new challenge of the day) for 7 different values and days.
Meaning I have one that has 6 days free but thereafter days 1-7 are @ 30 and
8+ is 80. So if can figure out how to change the formula to work new and
fewer values I'd be set.
youve done a ton, if this is to much to ask I absolutely understand. Thank
you so very much again
Bryan

"Sloth" wrote:

=IF(E16,IF(E110,IF(E131,1068+(E1-31)*100,68+(E1-10)*50),(E1-6)*17),0)

I think this formula will do it for you. Basically if E131 then it will be
1068+(E1-31)*100. 1068 represents 4*17+20*50 (the days under 31). The rest
of the formula follows this logic. Hope it helps. This doesn't count the
first 6 days, and outputs 0 if you have less than 6 days.

"Bryan J Bloom" wrote:

ok very well done and thank you all. But now i need to break it down a little
further if possible. Like I posted earlier this is showing days out of
rail/ship yard. What Im calculating is if the trailer is out of the RR more
than 6 days. Then days 7,8,9 and 10 are to be multiplied by 17. Then days 11
through 30 are to be multiplied by 50 and 31 + multiplied by 100. So the
formula you gave did the multiplication but multiplied (e) by only the
integer amount not by a rising number(my fault late info) I'm trying to get
it so that
eg: the trailer has been out 40 days(e). The first six are not multiplied.
Days
7,8,9 and 10 (4 days) are multiplied by 17(68). Then days 11-30 (the next 20
days) are multiplied by 50(1000). Finally days 31+ (the last days 10) are
multiplied by 100(1000) to be added together with the others to make a total
of 2068.
Sorry for any confusion, was asked to get it one way then get told
another..lol
Thanks so much
Bryan Bloom


"Sloth" wrote:

To add or subtract dates, treat them as regular numbers. Dates are in fact
stored as the number of days from 1/0/1900 (1/1/1900 is stored as 1). You
can check this by entering a date and then formating the cell as a number.
When you subtract a date from another date the result "looks" like a date,
but it is not. You need to change the cell format to a number after entering
the formula.

Example
A1-D1: various dates
I1-I3: various integers
E1: =(A1-B1) + (C1-D1)
F1: =IF(E16,IF(E110,IF(E131,I3*E1,I2*E1),E1*I1),E1)

F1 outputs:
E1 if E1<6
E1*I1 if 6<E1<10
E1*I2 if 10<E1<31
E1*I3 if E131

Be sure to format E1 and F1 as numbers after entering the formulas.

"Bryan J Bloom" wrote:

I need to subtract 2 different sets of dates to get a total amount of days.
Eg: (a-b)+(c-d) = e
I then need to take the total (e) and multiply by 3 different integers if
the total (e) is greater than 6 days,10 days and 31days.
This helps me keep track how long my company trucks are out of a Rail/Ship
Yard.
I can't seem to get the dates to subtract and get an integer(regular
number). and I'm dead lost on the greater than stuff.
Of course the boss drops this on me on my first day.
Any help or comments will come highly appreciated.
Thank you