Find out Latest Date

markjdm

New Member
Joined
Nov 26, 2013
Messages
8
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

markjdm

New Member
Joined
Nov 26, 2013
Messages
8
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,633
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?

 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top