Need a Macro to add Business Hours/Day

[naven_sg]

Hello All,

Sorry for the long description of the requirements, need your help in below situations; Where i want add Business 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.


Here is the Sample Data


Country Ticket Start Date/ Time Tgt Date/Time Expected Tgt Date/ Time

America ID000123 12/21/2012 18:20 12/27/2012 19:00

America ID000124 12/21/2012 6:54 12/26/2012 19:00

America ID000125 12/23/2012 8:20 12/27/2012 19:00

India ID000126 12/23/2012 21:20 12/26/2012 19:00

India ID000127 12/21/2012 15:18 12/24/2012 19:00

Australia ID000128 12/24/2012 13:20 12/27/2012 19:00

Australia ID000129 12/26/2012 13:20 12/28/2012 19:00

Australia ID000130 12/27/2012 12:20 12/28/2012 19:00


Holiday Table

Country Date Reason

America 12/24/2012 Chrismas Eve

America 12/25/2012 Chrismas

India 12/25/2012 Chrismas

Australia 12/25/2012 Chrismas

Australia 12/26/2012 Boxing Day

Great Britain 12/25/2012 Chrismas

Great Britain 12/26/2012 Boxing Day


Regards,

Naveen

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

 

[Sajan]

Hello Naveen,

The business rules you indicate seem to contradict the examples you provided.


Here is what I understood:

If a request is submitted before the Threshold, the target date/time is 7pm the next day.

If a request is submitted after the Threshold, the target date/time is 7pm two days from start date.

The target dates need to factor in holidays for the respective countries.


To simplify things, I set up Named ranges for holidays for each country separately. (The formula below assumes this setup. If you insist that the holidays need to be arranged exactly as you indicate in your sample, the formula will need to be revised.)


Type in "4:30 pm" somewhere in your workbook (without quotes), and give it the name "Threshold" (again, without quotes).


Assuming that A3 indicates country, B3 indicates StartDate/Time, then the following formula in C3 will provide the TargetDate/Time:

=WORKDAY(B3,IF(B3-INT(B3) < Threshold,1,2),CHOOSE(SUMPRODUCT((A3={"India","US","UK"})*{1,2,3}), Holidays_India, Holidays_US, Holidays_UK)) + "19:00"


Sample output using the above formula:

[pre]

Country	StartTime	        TargetTime
US	Dec 21, 2012 6:20 PM	27-Dec-12 07:00 PM
US	Dec 21, 2012 6:54 AM	26-Dec-12 07:00 PM
US	Dec 23, 2012 8:20 AM	26-Dec-12 07:00 PM
India	Dec 23, 2012 9:20 PM	26-Dec-12 07:00 PM
India	Dec 21, 2012 3:18 PM	24-Dec-12 07:00 PM

[/pre]
The formula segment "IF(B3-INT(B3) < Threshold,1,2)" determines whether to add 1 business day or 2 business days, depending on the Start Time being before or after the Threshold. If that is not the business rule you want, feel free to adjust it appropriately.


Cheers,

Sajan.

 

[Sajan]

Hello Naveen,


I had a few more minutes to look at this question...


To handle the holidays list in the format you supplied, you can use the following array formula in cell C3:

=WORKDAY(B3,IF(B3-INT(B3) < Threshold,1,2),Holidays_Dates*(Holidays_Countries=$A3)) + "19:00"

enter with Ctrl + Shift + Enter


(In this case, Holidays_Dates refers to the range of holidays, and Holidays_Countries refers to the range of the corresponding list of countries in your Holidays table.


All other comments from before still apply.


Cheers,

Sajan.