Inventory and Date Ranges
 

I have a large spreadsheet containing raw inventory data. The data has
inventory spanning calendar years (2005 - 2006). I need to account for 2006
inventory only. A sample of what my data looks like: 9,000,000 units sold
from 9/12/05 - 3/15/06. I need to write a formula that will evenly break out
the 2006 units into months (daily units x days in month). I can manually do
this for several lines, but I have thousands of lines of data. What formula
can I use to read the date range and evenly distribute the units across
months?

Thanks!

-TK

Inventory and Date Ranges
 

You have not told use what you data looks like
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"TK" wrote in message
...
I have a large spreadsheet containing raw inventory data. The data has
inventory spanning calendar years (2005 - 2006). I need to account for
2006
inventory only. A sample of what my data looks like: 9,000,000 units sold
from 9/12/05 - 3/15/06. I need to write a formula that will evenly break
out
the 2006 units into months (daily units x days in month). I can manually
do
this for several lines, but I have thousands of lines of data. What
formula
can I use to read the date range and evenly distribute the units across
months?

Thanks!

-TK

Inventory and Date Ranges
 

This is what my data looks like.....

A B C
D
1 Start Date End Date Quantitiy Days in Range
2 9/12/05 3/15/06 9,000,000 185
3 12/18/05 1/17/06 1,000,000 31

I need a formula distributing the quantity evenly into the applicable
months. If I was to do this manually, I would just write =(9,000,000/185*31)
for january inventory in row 2. Any ideas for a formula like this that can
read the start and end dates?

"Bernard Liengme" wrote:

You have not told use what you data looks like
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"TK" wrote in message
...
I have a large spreadsheet containing raw inventory data. The data has
inventory spanning calendar years (2005 - 2006). I need to account for
2006
inventory only. A sample of what my data looks like: 9,000,000 units sold
from 9/12/05 - 3/15/06. I need to write a formula that will evenly break
out
the 2006 units into months (daily units x days in month). I can manually
do
this for several lines, but I have thousands of lines of data. What
formula
can I use to read the date range and evenly distribute the units across
months?

Thanks!

-TK

Inventory and Date Ranges
 

If your end-date such as 3/15/06 is in a cell by itself, then you can just
subtract 38717 (the Excel code for 12/31/05) from it (formatted to general),
to get the 74 days difference.

If your end-date is not in a cell by itself, it will have to be extracted
using the TEXT functions first.


hth
Vaya con Dios,
Chuck, CABGx3

"TK" wrote:

I have a large spreadsheet containing raw inventory data. The data has
inventory spanning calendar years (2005 - 2006). I need to account for 2006
inventory only. A sample of what my data looks like: 9,000,000 units sold
from 9/12/05 - 3/15/06. I need to write a formula that will evenly break out
the 2006 units into months (daily units x days in month). I can manually do
this for several lines, but I have thousands of lines of data. What formula
can I use to read the date range and evenly distribute the units across
months?

Thanks!

-TK