Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days by month and IF statement.
If A1 = 6/1/2006, can B1 = the number of days in June w/o using a VLOOKUP?
Im trying to embed a couple of conditions in an IF statement and i don't know where to begin. I have a fixed volume of 500/day; July = 31 days; total 15500 for july. I need to charge .442 for the first 10000 per unit, 1.327 for the next 100 units, 1.098 forthe next 140, and .9255 for the remaining units, which in this case are 5260. I made A1 = 500x31 (15500) and then have the formula point at it. How can i make C1 read A1 and then give me the correct output for that volume? Thank you for all your help in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days by month and IF statement.
If you have the analysis toolpak installed, you can use eomonth:
=DAY(EOMONTH(A1,0)). If you don't have the ATP, =day(date(year(a1),month(a1)+1,0)) will get you the same result. "Daniel Q." wrote: If A1 = 6/1/2006, can B1 = the number of days in June w/o using a VLOOKUP? Im trying to embed a couple of conditions in an IF statement and i don't know where to begin. I have a fixed volume of 500/day; July = 31 days; total 15500 for july. I need to charge .442 for the first 10000 per unit, 1.327 for the next 100 units, 1.098 forthe next 140, and .9255 for the remaining units, which in this case are 5260. I made A1 = 500x31 (15500) and then have the formula point at it. How can i make C1 read A1 and then give me the correct output for that volume? Thank you for all your help in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days by month and IF statement.
Daniel Q. Wrote: If A1 = 6/1/2006, can B1 = the number of days in June w/o using a VLOOKUP? Im trying to embed a couple of conditions in an IF statement and i don't know where to begin. I have a fixed volume of 500/day; July = 31 days; total 15500 for july. I need to charge .442 for the first 10000 per unit, 1.327 for the next 100 units, 1.098 forthe next 140, and .9255 for the remaining units, which in this case are 5260. I made A1 = 500x31 (15500) and then have the formula point at it. How can i make C1 read A1 and then give me the correct output for that volume? Thank you for all your help in advance. Not sure if you meant B1 or C1, but try this: =DATE(YEAR(A1)+IF(MONTH(A1)=12,1,0),IF(MONTH(A1)=1 2,1,MONTH(A1)+1),1)-A1 You'll have to switch the format to number, since it assumes you want it to be a date format. Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=563463 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days by month and IF statement.
To find the number of days in a month, use this formula: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) Then just do that*500 To be honest with you, the easiest way to calculate charge is this: =4706.42+(C1-10240)*.9255 The reason why is because EVERY month, even if it only has 28 days, you will do over 10240 units, so you just want to find out what is the remainder and multiple it by .9255 Ok. so put it all together in 1 formula: =4706.42+(DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-10240)*.9255 Not pretty, but it works -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563463 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
holiday dates | Excel Worksheet Functions | |||
function to fill all days of month to end of month | Excel Worksheet Functions | |||
Calculate Days in a Month | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Calculate running target by days in the month | Excel Worksheet Functions |