Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Data That Lies Between Two Dates
Hi guys.
I have a spreadsheet which details a list of fixed assets that mature over a period of time. Now i need to know which items fall into which categories without having to work it out manually! An example of my spreadsheet is: Item Acquisition Fully Dep'n Cost Current Depn WDA x 01/05/06 30/04/11 300 250 50 I have the current date in cell J1 and the depreciation is calculated from this, now imagine the list being very long full of different items fully depreciating at different times. I need to put the current WDA figure of each item into one of the following groups dependant on when that item is fully depreciated: 0-3Months 3-6Months 6-12Months 1-5years Over 5years Please help! Thanks. Tom. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Data That Lies Between Two Dates
If you set up a table like this:
0 0-3Months 91 3-6Months 182 6-12Months 365 1-5years 1825 Over 5years (assume this is in X1:Y5), then you can use ths formula: =VLOOKUP(C2-B2,X$1:Y$5,2) where C2 is the Fully Dep'n date and B2 is the Acquisition date, and this will give you the group description. Copy the formula down as far as required. Hope this helps. Pete On Dec 12, 9:53*am, Bazy2k wrote: Hi guys. I have a spreadsheet which details a list of fixed assets that mature over a period of time. Now i need to know which items fall into which categories without having to work it out manually! An example of my spreadsheet is: Item * * * * * *Acquisition * Fully Dep'n * * * Cost * * Current Depn * * WDA x * * * * * * * * 01/05/06 * * *30/04/11 * * * * 300 * * * * * * * 250 * * * * * *50 I have the current date in cell J1 and the depreciation is calculated from this, now imagine the list being very long full of different items fully depreciating at different times. I need to put the current WDA figure of each item into one of the following groups dependant on when that item is fully depreciated: 0-3Months 3-6Months 6-12Months 1-5years Over 5years Please help! Thanks. Tom. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Data from Date Range base on relative dates | Excel Worksheet Functions | |||
How to make cell width different than the column width it lies in | Excel Discussion (Misc queries) | |||
Finding the cell in which an object lies | Charts and Charting in Excel | |||
Adding Column of mixed data omitting the dates | Excel Discussion (Misc queries) | |||
Adding dates | Excel Worksheet Functions |