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

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

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

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

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