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
    PKJ

  • Re: Aging Inventory


    So, if the item was used in the last 30 days, the bucket is 0-30days
    and if the item was uned in the last 60 days, the bucket is 0-30 days?


    I am not sure I understand your question.


    what about =IF(TODAY()-"Last used date" <= 30, "0-30Days",IF(TODAY()-"Last used date" <= 60,"31-60Days",">60Days")))

    Regards,


    WidgetWonka
    Puuuureeee Imagination

  • Re: Aging Inventory


    Thanks for your help. Here is the table. The Age below should look at both Part#, Days and bucket the categories accordingly. Though C4871A has 282, 254 and 8 days, the age is still 0-30Days(because the latest usage is less than 30 days old). Likewise, C4872A has both 313 and 36 days, the age is 31-60days and so on......hope you got me now....thank you very much for your help.


    [TABLE="width: 513"]

    [tr]


    [td]

    Part#

    [/td]


    [td]

    Used Date

    [/td]


    [td]

    Days

    [/td]


    [td]

    Age

    [/td]


    [/tr]


    [tr]


    [td]

    C4871A

    [/td]


    [td]

    2/1/2011

    [/td]


    [td]

    282

    [/td]


    [td]

    0-30Days

    [/td]


    [/tr]


    [tr]


    [td]

    C4871A

    [/td]


    [td]

    3/1/2011

    [/td]


    [td]

    254

    [/td]


    [td]

    0-30Days

    [/td]


    [/tr]


    [tr]


    [td]

    C4872A

    [/td]


    [td]

    10/5/2011

    [/td]


    [td]

    36

    [/td]


    [td]

    31-60Days

    [/td]


    [/tr]


    [tr]


    [td]

    C4872A

    [/td]


    [td]

    1/1/2011

    [/td]


    [td]

    313

    [/td]


    [td]

    31-60Days

    [/td]


    [/tr]


    [tr]


    [td]

    C4873A

    [/td]


    [td]

    11/5/2011

    [/td]


    [td]

    5

    [/td]


    [td]

    0-30Days

    [/td]


    [/tr]


    [tr]


    [td]

    C4873A

    [/td]


    [td]

    5/1/2011

    [/td]


    [td]

    193

    [/td]


    [td]

    0-30Days

    [/td]


    [/tr]


    [tr]


    [td]

    C4874A

    [/td]


    [td]

    10/4/2011

    [/td]


    [td]

    37

    [/td]


    [td]

    31-60Days

    [/td]


    [/tr]


    [tr]


    [td]

    C4871A

    [/td]


    [td]

    11/2/2011

    [/td]


    [td]

    8

    [/td]


    [td]

    0-30Days

    [/td]


    [/tr]


    [/TABLE]

  • Re: Aging Inventory


    I put your values in a spreadsheet, starting in A1. Here is the formula for cell D1:


    {=IF(SMALL(IF(A2=$A$2:$A$1000,$C$2:$C$1000,10000),1)<=30,"0-30Days",IF(SMALL(IF(A2=$A$2:$A$1000,$C$2:$C$1000,10000),1)<=60,"31-60Days",">60Days"))}


    This is an array function. To enter it, enter everything but the brackets. Then, hold ctrl+shift while pressing enter to exit the cell. The formula creates an array, and then find the smallest value. I set the alternative value to 10000, which should be OK given your buckets.

    Regards,


    WidgetWonka
    Puuuureeee Imagination

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!