Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inventory and Date Ranges | Excel Worksheet Functions | |||
Urgent! Somebody Pleez help!! Inventory Valuation | Excel Worksheet Functions | |||
Best template to inventory toy collection w/pictures included | Excel Discussion (Misc queries) | |||
Estimated date of completion | Excel Worksheet Functions | |||
How do I change a date in a cell | Excel Worksheet Functions |