Hi
This does it in VBA with a bit of added sense checking. :-
Function AgeBucket(vCell As Variant) As Variant
Application.Volatile
'
Select Case DateDiff("d", vCell, Now)
Case Is < 0: AgeBucket = "?"
Case Is <= 30: AgeBucket = "A"
Case Is <= 60: AgeBucket = "B"
Case 61 To 10 * 365: AgeBucket = "C"
Case Else: AgeBucket = "??"
End Select
End Function
Del +++
>________________________________
>From: David Smart <smartware.consulting@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, 10 November 2011, 20:59
>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