# Find out Latest Date

#### markjdm

##### New Member
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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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

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?

Replies
3
Views
288
Replies
12
Views
485
Replies
24
Views
773
Replies
8
Views
624
Replies
1
Views
259

1,217,382
Messages
6,136,239
Members
450,000
Latest member
jgp19

### 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.

### Which adblocker are you using?

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

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