Kumar
This sounds like you mean that each use of an item is recorded in its own row and that all records for each item must be altered to match the earliest ?
Del +++
>________________________________
>From: "david.smart@ubs.com" <david.smart@ubs.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Friday, 11 November 2011, 7:52
>Subject: RE: [ExcelVBA] Aging Inventory
>
>
>
>> However, for a situation below, it would be good to have only 0-30Days
>in both C2 and C3 cells.
>
>I don't really understand what you mean here. I gave you formulas for
>four columns and said that they should be copied down.
>
>I didn't actually give any information for rows 2 or 3, just row 1. You
>would fill all the other rows with your formula copy.
>
>> In this case, it just displays the last usage date.
>
>Again, not sure what you mean. My first set of formulas will display
>the last usage date in C or D or E depending on the age of the usage.
>You will probably need to put the formulas in some other columns ...
>I've just given examples.
>
>My second set of formulas displayed the number of days since last use,
>but again in the appropriate column C or D or E.
>
>> Also, doesn't the PN# be part of the condition?
>
>I don't see why it would be. We're working down the rows here and each
>row will presumably represent a different item. Reading across the row,
>you can see the aging of the last usage in C or D or E (from my
>examples, or whatever columns you choose to use).
>
>
>
>
>
>You didn't read my four formulas as going into different ROWS, did you?
>They are intended to go into different COLUMNS on the same row (which is
>row 1 in my examples).
>
>Regards, Dave S
>
>________________________________
>
>From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
>Behalf Of Prasanna Kumar
>Sent: Friday, 11 November 2011 09:07
>To: ExcelVBA@yahoogroups.com
>Subject: Re: [ExcelVBA] Aging Inventory
>
>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
><mailto:smartware.consulting%40gmail.com> >
>To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.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 <mailto:pkj7461%40yahoo.com> >
>To: <ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.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]
>
>----------
>
>Visit our website at http://www.ubs.com
>
>This message contains confidential information and is intended only
>for the individual named. If you are not the named addressee you
>should not disseminate, distribute or copy this e-mail. Please
>notify the sender immediately by e-mail if you have received this
>e-mail by mistake and delete this e-mail from your system.
>
>E-mails are not encrypted and cannot be guaranteed to be secure or
>error-free as information could be intercepted, corrupted, lost,
>destroyed, arrive late or incomplete, or contain viruses. The sender
>therefore does not accept liability for any errors or omissions in the
>contents of this message which arise as a result of e-mail transmission.
>If verification is required please request a hard-copy version. This
>message is provided for informational purposes and should not be
>construed as a solicitation or offer to buy or sell any securities
>or related financial instruments.
>
>UBS reserves the right to retain all messages. Messages are protected
>and accessed only in legally justified cases.
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[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