Need help with a formula

[Bryan J Bloom]

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

[Peo Sjoblom]

One way

=A1*VLOOKUP(A1,{0,0;7,4;11,3;32,2},2)

where A1 is your e, 7 stands for >6, 11 stands for >10 and 32 for >31
4 is what you multiply with for >6, 3 for >10 and 2 >31
replace those with your numbers.


--

Regards,

Peo Sjoblom

"Bryan J Bloom" <Bryan J Bloom@discussions.microsoft.com> wrote in message
news:35AA1B3F-5155-4CB8-BE6C-6E0831EA09D6@microsoft.com...
> 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
>

[Bryan J Bloom]

ok very well done and thank you. 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) I'm trying to get it so that
eg: the trailers 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 the next 20 days are
multiplied by 50(1000). Finally the last days 10 are multiplied by 100(1000)
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

"Peo Sjoblom" wrote:

> One way
>
> =A1*VLOOKUP(A1,{0,0;7,4;11,3;32,2},2)
>
> where A1 is your e, 7 stands for >6, 11 stands for >10 and 32 for >31
> 4 is what you multiply with for >6, 3 for >10 and 2 >31
> replace those with your numbers.
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "Bryan J Bloom" <Bryan J Bloom@discussions.microsoft.com> wrote in message
> news:35AA1B3F-5155-4CB8-BE6C-6E0831EA09D6@microsoft.com...
> > 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
> >
>
>
>

[Peo Sjoblom]

You should be able to adapt this to fit your needs

http://www.mcgimpsey.com/excel/variablerate.html


--

Regards,

Peo Sjoblom

"Bryan J Bloom" <BryanJBloom@discussions.microsoft.com> wrote in message
news:2E59994D-C5D3-4F13-B7DA-7DDBDB1606DC@microsoft.com...
> ok very well done and thank you. 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) I'm trying to get it so
that
> eg: the trailers 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 the next 20 days are
> multiplied by 50(1000). Finally the last days 10 are multiplied by
100(1000)
> 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
>
> "Peo Sjoblom" wrote:
>
> > One way
> >
> > =A1*VLOOKUP(A1,{0,0;7,4;11,3;32,2},2)
> >
> > where A1 is your e, 7 stands for >6, 11 stands for >10 and 32 for >31
> > 4 is what you multiply with for >6, 3 for >10 and 2 >31
> > replace those with your numbers.
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "Bryan J Bloom" <Bryan J Bloom@discussions.microsoft.com> wrote in
message
> > news:35AA1B3F-5155-4CB8-BE6C-6E0831EA09D6@microsoft.com...
> > > 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
> > >
> >
> >
> >

[Roger Govier]

Hi Bryan

Answer posted in Excel.Misc

Regards

Roger Govier


Bryan J Bloom wrote:
> ok very well done and thank you. 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) I'm trying to get it so that
> eg: the trailers 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 the next 20 days are
> multiplied by 50(1000). Finally the last days 10 are multiplied by 100(1000)
> 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
>
> "Peo Sjoblom" wrote:
>
>
>>One way
>>
>>=A1*VLOOKUP(A1,{0,0;7,4;11,3;32,2},2)
>>
>>where A1 is your e, 7 stands for >6, 11 stands for >10 and 32 for >31
>>4 is what you multiply with for >6, 3 for >10 and 2 >31
>>replace those with your numbers.
>>
>>
>>--
>>
>>Regards,
>>
>>Peo Sjoblom
>>
>>"Bryan J Bloom" <Bryan J Bloom@discussions.microsoft.com> wrote in message
>>news:35AA1B3F-5155-4CB8-BE6C-6E0831EA09D6@microsoft.com...
>>
>>>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
>>>
>>
>>
>>

[JE McGimpsey]

One way:

E1: =(A1-B1+C1-D1)

Format E1 as General or another number format

F1: =IF(E1<=6,"",E1*LOOKUP(E1,{7,11,31},{1,2,3}))

where 1,2,3 are your three different integers.

In article <35AA1B3F-5155-4CB8-BE6C-6E0831EA09D6@microsoft.com>,
"Bryan J Bloom" <Bryan J Bloom@discussions.microsoft.com> 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