Inventory and Date Ranges

[Guest]

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

 

[Bernard Liengme]

You have not told use what you data looks like

 

[Guest]

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?

 

[Guest]

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