You say "the formula", but be aware that you will need one for each bucket.
You might find it a bit easier if you put a helper column in to hold the
days since use. This is a simple subtraction:
If column A is the last used date and column B is the helper:
B1 is =TODAY()-A1
C1 is =IF($B1<=30,$A1,"")
D1 is =IF(AND($B1>30,$B1<=60),$A1,"")
E1 is =IF($B1>60,$A1,"")
and these formulas copy down as needed.
Excel will probably get the cell formatting wrong. You need to change the
helper column to display in General format, not date. You also need to get
C, D and E to display the last used date in date format if it simply gives
you a number.
If you want the number of days since last used in the aging columns instead,
then
C1 is =IF($B1<=30,$B1,"")
D1 is =IF(AND($B1>30,$B1<=60),$B1,"")
E1 is =IF($B1>60,$B1,"")
and format as General if Excel formats as a date.
Regards, Dave S
----- Original Message -----
From: "pkj7461" <pkj7461@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Friday, November 11, 2011 5:19 AM
Subject: [ExcelVBA] Aging Inventory
> Hello,
>
> I hope someone helps me with Aging the inventory based on usage. I have
> the usage data and am trying to create buckets of 0-30Days, 31-60Days,
> >60Days based on the date used.
> For example, if part#C4871A is used in last 30 days, it should be in
> 0-30Days. Even if this item is used in last 60 days, the aging should be
> still set as 0-30Days. Can you please help me with the formula for this
> type of aging?
>
> Thanks
> Kumar.
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1411 / Virus Database: 1522/4007 - Release Date: 11/09/11
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar