Home |
Search |
Today's Posts |
#1
|
|||
|
|||
need help with formula
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 |
#2
|
|||
|
|||
need help with formula
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 Hi Bryan To subtract your dates try this in E1 =((A1-B1)+(C1-D1)), format the cell as general For the other stuff try this =IF(E1<6,"",IF(AND(E1=6,E1<=10),E1*1,IF(AND(E110 ,E1<=31),E1*2,E1*3))) change the *1, *2, & *3 to be the 3 integers you want to use -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=480577 |
#3
|
|||
|
|||
need help with formula
Bryan,
Try this to test the value in C4 =IF(C431,C4*Factor31,IF(C410,C4*Factor10,IF(C46 ,C4*Factor6,0))) Where Factor31 is the name given to cell C5, Factor10 is the name given to cell C6 and Factor5 is the name given to cell C7. The ,0 at the end of the formula tells it what to do if none of the three conditions tested for are met. It couls just as easi;ly be a reference to a cell a name or a label e.g. "None" You could just put values in the formula instead of references to worksheet name, but if you plan to copy the formula to a large number of cells, it's harder to modify, as you'd have to change each one if the 6, 10 and 31 factors ever needed to change. This way, you only need to change the values in the "Factor" cells. No idea why you can't get an integer result, unless you're trying to subtract today's date NOW() somewhere, which will always be a decimal, (apart fro 00:00 midnight!) depending on where you are in the system clock. if this is the case, try using INT(NOW()) in your formula, to retuen a whole number. Don''t forget to test largest first - i.e. you want to test that the value is greater than 31 before you test that it's greater than 10 or 6 etc) Hope this helps. Pete "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 |
#4
|
|||
|
|||
need help with formula
Hi Bryan,
There's a function called DATEDIF that is not listed in the regular functions list that can be used to get the difference between the dates; =DATEDIF(A1,B1,"D") with the date in A1 being older than the date in B1, and "D" specifying that you want the number of days between the two dates. This formula can be used for both sets of dates: =DATEDIF(A1,B1,"d")+DATEDIF(A2,B2,"d"). You may need to format the cell as General if the result comes up a date; that should give you your number. The formula below assumes the number of days between the dates in in cell C1. =IF(AND(C16,C1<10),C1*1,IF(AND(C110, C1<31),C1*2,IF(C131,C1*3))) Hope this helps you out. "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 |
#5
|
|||
|
|||
need help with formula
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 |
#6
|
|||
|
|||
need help with formula
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 |
#7
|
|||
|
|||
need help with formula
=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 |
#8
|
|||
|
|||
need help with formula
Hi Bryan
With your calculated days being in K2 =MAX(0,K2-6)*17+MAX(0,K2-10)*33+MAX(0,K2-30)*50 Change cell ID holding days out to suit. Regards Roger Govier 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 |
#9
|
|||
|
|||
need help with formula
Oops. That formula is slightly wrong.
=IF(A416,IF(A4110,IF(A4130,1068+(A41-30)*100,68+(A41-10)*50),(A41-6)*17),0) In your first post you said 31 in this last post you desribed 30. This formula matches your last post. So... day 10 adds 17 day 11 adds 50 day 30 adds 50 day 31 adds 100 let me know if this isn't correct, I will change it accordingly if you need me too. "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 |
#10
|
|||
|
|||
need help with formula
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 |
#11
|
|||
|
|||
need help with formula
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 |
#12
|
|||
|
|||
need help with formula
Hi
Maybe just have a table with your parameters A1=6, A2=30, B1=13, B2=50 and so on, always making the next set of cells in the series the increment from the values below, not the absolute values themselves. Then =MAX(0,K2-A1)*A2+MAX(0,K2-B1)*B2 Change the values in A1:B2 at any time to alter the calculation. Regards Roger Govier Sloth wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |