Thanks David. However, for a situation below, it would be good to have only 0-30Days in both C2 and C3 cells. In this case, it just displays the last usage date. Also, doesn't the PN# be part of the condition?
Thanks for your help.
Usage Date Days Age Part#
11/2/2011 8 11/2/2011 C4871A
10/2/2011 39 10/2/2011 C4871A
From: David Smart <smartware.consulting@gmail.com>
To: ExcelVBA@yahoogroups.com
Sent: Thursday, November 10, 2011 12:59 PM
Subject: Re: [ExcelVBA] Aging Inventory
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
>
[Non-text portions of this message have been removed]
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