Posts by naven_sg

    Hello all,


    Please help me with the below request. In the below table there are some date and Serial numbers. What i want is that count of serial numbers which re-appeared within 30 days from Date. Ex: The Serial number which appeared on 8/6/2013 L3AAB8Y - 350M same as reappeared within 30 days on 9/1/2013 and that should be counted. However another serial number which is appeared on 8/1/2013 L3AGD7K - 35ZG is again reappeaed after 30 days should not be counted.


    I hope i have explained my situation clearely here. Advance thanks ...NG
    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Date

    [/td]


    [td]

    Combine Serial

    [/td]


    [/tr]


    [tr]


    [td]

    8/6/2013

    [/td]


    [td]

    L3AAB8Y - 350M

    [/td]


    [/tr]


    [tr]


    [td]

    8/7/2013

    [/td]


    [td]

    L3AGD7K - 23JC

    [/td]


    [/tr]


    [tr]


    [td]

    8/1/2013

    [/td]


    [td]

    L3AGD7K - 35ZG

    [/td]


    [/tr]


    [tr]


    [td]

    8/8/2013

    [/td]


    [td]

    [TABLE="width: 185"]



    [tr]


    [TD="align: right"]

    [/tr][/td][tr]


    [td]

    L3AGF8Y - 3FLP

    [/td]


    [/tr]



    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    9/1/2013

    [/td]


    [td]

    L3AAB8Y - 350M

    [/td]


    [/tr]


    [tr]


    [td]

    9/1/2013

    [/td]


    [td]

    L3AGD7K - 23JC

    [/td]


    [/tr]


    [tr]


    [td]

    9/10/2013

    [/td]


    [td]

    L3AGD7K - 35ZG

    [/td]


    [/tr]


    [/TABLE]

    Re: Macro to add Buisiness Hours/Days to Date


    Hello Herbds7,


    Thanks for your efforts, but Priority calculation is wrong. In the above worksheet you have considered ND = 11 hours. However ND 1 Day + a maximum of 11 hours.


    Here is an Example:


    A ticket opened on 12/27/2012 8:56 AM = To get the tgt date 1 Day + 10:04 hours should be added to get the Tgt date i.e 12/28/2012 19:00.


    Like wise If it is 3 BD it will 3 Business Days + a maximum of 11 hours.


    In the Test xl file i have also given the "Expected Tgt Dt/Time", this is nothing but the result which want at the end.


    I really appreciate your efforts here, can you please adjust this.



    Regards,


    NG

    Hello All,
    Sorry for the long description of the requirements, need your help in below situations; Where i want add Buisiness Hours/Days to get the Target Date.
    Working hours: 8:00 AM to 19:00 PM
    Urgency Type:
    ND (Maximum 1 Day 11 hours)
    3D (Maximum 3 Day 11 hours)
    4D (Maximum 4 Day 11 hours)
    5D (Maximum 5 Day 11 hours)
    Threshhold - 16:30 PM
    Weekends to be Excluded.
    Holidays to be Excluded based on Country. Say I have 2 countries India and America, where America has holiday on 12/24/2012 but India does not have holiday on that Day. Hence while calculating Tgt date, India should be calculated normally, for America holiday date need to excluded(There will be a Holiday table based on the countries, not limited to 2 countries).
    ex: 1) A ticket opened at 12/26/2012 8:56 AM; Target date will be 12/26/2012 19:00, in this case its 1 day 10:04 hours added to the Opened Time.
    2) A ticket opened at 12/26/2012 16:56 PM; Target date will be 12/29/2012 19:00, in this case ticket opened after threshold time of 16:30, Technically ticket should start from next day i.e 12/28/2012 8:00 AM and 1 day 11 hours added to this date.
    3) A ticket opened at 12/26/2012 7:56 AM(Before the begin of Working hours); Target date will be 12/26/2012 19:00, in this case its 1 day 11:00 hours added to the Opened Time.
    4) A ticket opened at 12/21/2012 17:56 PM(After Threshold time); Target date will be 12/24/2012 19:00, in this case its 1 day 11:00 hours added to the Opened Time. If the above ticket belonged to America, then holiday dates(both 24 & 25) need to be skipped and Tgt date will be 12/27/2012 19:00.


    I have attached Test Xl file with a sample data. I need results to be appear in the "Tgt Date/Time" column. I have also provided results in "Expected Tgt Date/ Time" for your help.


    Advance thanks for all your efforts.
    Regards,
    NG