Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates problem
i have got a problem..i have 3 columns in excel-startdate,end date and volume
i need to distribute the volume amongst two months ..for ex start date os oct 27 and end date is 7th nov, i need to distribute the volume by finding the volume per day or prorating the days and then put some volume in oct and the rest in nov... how do i do that??????????? pls help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates problem
Say Start date is in A2, end date in B2, volume in C2
month 1 volume: =$C2/($B2-$A2+1)*(DATE(YEAR($A2),MONTH($A2)+1,0)-$A2+1) month 2 volume: =$C2/($B2-$A2+1)*(DAY($B2)) See example: http://cjoint.com/?gcl7jWXoDJ This works only if 2 consecutive months are involved. HTH -- AP "mita" a écrit dans le message de news: ... i have got a problem..i have 3 columns in excel-startdate,end date and volume i need to distribute the volume amongst two months ..for ex start date os oct 27 and end date is 7th nov, i need to distribute the volume by finding the volume per day or prorating the days and then put some volume in oct and the rest in nov... how do i do that??????????? pls help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates problem
Hi
Tough one!! With your start date in A1, your end date in B1 and your volume in C1, try this in A2: =C1/(B1-A1+1)*(DATE(YEAR(B1),MONTH(B1),0)-A1+1) to give you your volume for the month of the start date. To get the volume for the other month, you can simply use =C1-A2 in cell B2. Hope this helps. Andy. "mita" wrote in message ... i have got a problem..i have 3 columns in excel-startdate,end date and volume i need to distribute the volume amongst two months ..for ex start date os oct 27 and end date is 7th nov, i need to distribute the volume by finding the volume per day or prorating the days and then put some volume in oct and the rest in nov... how do i do that??????????? pls help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates problem
thanks for that andy..it has surely helped.well i have got thousands of rows
in this table and i need to prorate the data for all the months in a calendar.. for ex i have created 12 additional in this table for each month.. so do u think this formula will work for each month in the table??? thanks "Andy" wrote: Hi Tough one!! With your start date in A1, your end date in B1 and your volume in C1, try this in A2: =C1/(B1-A1+1)*(DATE(YEAR(B1),MONTH(B1),0)-A1+1) to give you your volume for the month of the start date. To get the volume for the other month, you can simply use =C1-A2 in cell B2. Hope this helps. Andy. "mita" wrote in message ... i have got a problem..i have 3 columns in excel-startdate,end date and volume i need to distribute the volume amongst two months ..for ex start date os oct 27 and end date is 7th nov, i need to distribute the volume by finding the volume per day or prorating the days and then put some volume in oct and the rest in nov... how do i do that??????????? pls help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
dates problem
Hi
My formula is exactly the same as Ardus' in his post. His uses the next two columns for the results and mine uses the cells underneath. If you have loads of these, you may be better using the extra columns that Ardus' formula uses. As he put in his post, it will work as long as the two months are consecutive - which is the same as mine! Just fill in the first two and try auto-filling it down the column and see what you get. Hope this helps. Andy. "mita" wrote in message ... thanks for that andy..it has surely helped.well i have got thousands of rows in this table and i need to prorate the data for all the months in a calendar.. for ex i have created 12 additional in this table for each month.. so do u think this formula will work for each month in the table??? thanks "Andy" wrote: Hi Tough one!! With your start date in A1, your end date in B1 and your volume in C1, try this in A2: =C1/(B1-A1+1)*(DATE(YEAR(B1),MONTH(B1),0)-A1+1) to give you your volume for the month of the start date. To get the volume for the other month, you can simply use =C1-A2 in cell B2. Hope this helps. Andy. "mita" wrote in message ... i have got a problem..i have 3 columns in excel-startdate,end date and volume i need to distribute the volume amongst two months ..for ex start date os oct 27 and end date is 7th nov, i need to distribute the volume by finding the volume per day or prorating the days and then put some volume in oct and the rest in nov... how do i do that??????????? pls help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates Problem | Excel Discussion (Misc queries) | |||
formula to add dates. | Excel Worksheet Functions | |||
US dates to UK?? | Excel Worksheet Functions | |||
Counting dates for a the present month but not future months | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |