Find out Latest Date

[markjdm]

Hi,

I was trying to get an answer to the below question but can't seem to figure it out and couldn't find anything similar to it.

I have to find out the date I can cancel an order based on specific rules.

Scenario: I have a order date of 22/12/2016 at 8:00am.

1. I need to know the latest date/time I can cancel this order before it exceeds our SLA.
2. It will need to calculate 34 business hours prior to the date/time of order.
3. Business hours are counted as M-F between 8am-5pm only. No weekends.

Ideally a VBA solution to calculate this would be good.

Thanks,
Mark

 

[Tetra201]

What's the expected answer?

 

[markjdm]

Hi Tetra201,

Sorry I realized my business rules are wrong, here are the requirements and expected results.

1. I need to know the latest date/time I can cancel this order before it exceeds our SLA.
2. It will need to calculate 34 hours (non business hours) prior to the date/time of order.
3. Not count any hours that fall on a Saturday/Sunday.

No Weekends before completion date:
Scenario 1: Order/Completion Date is 22/12/2016 at 8:00
Expected Result: Can be cancelled up until 20/12/2016 at 24:00

Weekend before completion date:
Scenario 2: Order/Completion Date is 20/12/2016 at 13:00
Expected Results: Can be cancelled up until 16/12/2016 at 5:00

 

[Tetra201]

It's still unclear:

(22/12/2016 8:00) - (20/12/2016 24:00) = 32 hours

(20/12/2016 13:00) - (16/12/2016 5:00) - (Sun_hours) - (Sat_hours) = 56 hours

What's the logic behind this?