Calculate Due Time in Date and Hours:Minutes excluding Weekends and Public/State Holiday

[jennyzhou26]

Hi,
Just need formulas to calculate the date and time P3 and P4 work orders that have two types of deadlines.

E.g P4 have 5 business days deadline for response time, and 10 business days until it must be completed. P4 received at 24/05/17 10:30am, has Response time due on 31/05/17 10:30am, and Completion time due due 07/06/17 10:30am. I need formulas that can calculate the response time and completion time but with me manually typing in P4, 24/05/17 10:30am... We receive 50 P4 work orders a week so having it automated using excel like this would be great.

I dont want a timer, just formulas that can calculate the deadlines to the date, hour and minute

 

[Trevor_S]

I'm assuming that this is for use in the same spreadsheet as your other thread https://www.mrexcel.com/forum/excel...me-completion-time-deadlines-work-orders.html ? If so, you'll want one formula that covers both scenarios - adding hours or days.

Adding days is easy, simple formula to add a number to the date cell. Adding hours needs to use the time function, TIME(1,0,0) represents one hour - the arguments being hours, minutes and seconds.

To combine these, use a CHOOSE function. The first argument is an index number, and the subsequent ones are what to do for that number. So, say that:

• your base date is in cell A1,
• your work order type is in cell B1,
• work order types P1 adds one hour, P2 adds two hours, P3 adds three hours and P4 adds one day,

your formula would be:
=A1+CHOOSE(RIGHT(B1,1),TIME(1,0,0),TIME(2,0,0),TIME(3,0,0),1)

Your title (but not post) mentions weekends and state holidays. If this is needed, weekends could be adjusted for, as they are always set days - but does your spreadsheet hold a list of state holidays?

 

[jennyzhou26]

I'm assuming that this is for use in the same spreadsheet as your other thread https://www.mrexcel.com/forum/excel...me-completion-time-deadlines-work-orders.html ? If so, you'll want one formula that covers both scenarios - adding hours or days.

Adding days is easy, simple formula to add a number to the date cell. Adding hours needs to use the time function, TIME(1,0,0) represents one hour - the arguments being hours, minutes and seconds.

To combine these, use a CHOOSE function. The first argument is an index number, and the subsequent ones are what to do for that number. So, say that:

• your base date is in cell A1,
• your work order type is in cell B1,
• work order types P1 adds one hour, P2 adds two hours, P3 adds three hours and P4 adds one day,

your formula would be:
=A1+CHOOSE(RIGHT(B1,1),TIME(1,0,0),TIME(2,0,0),TIME(3,0,0),1)

Your title (but not post) mentions weekends and state holidays. If this is needed, weekends could be adjusted for, as they are always set days - but does your spreadsheet hold a list of state holidays?

Hi, Yes, the P4 work orders only count business days, so it excludes weekends and national/state holidays. I guess with holidays, I have to manually adjust them?

 

[jennyzhou26]

Is there a way to show this through pictures/photos.

 

[Trevor_S]

I believe that images can be uploaded to forums, although I've never done it. Alternatively, files can be saved to a file sharing site like google drive, and URL's pasted in threads. Just make sure that whatever is uploaded doesn't included confidential/sensitive data.

Just thinking, is there a similar issue with the working day on the codes that add a number of hours? If a call is logged near the end of a day, does a four hour response time run into the following day?

 

[jennyzhou26]

P1 and P2 are urgent jobs, so the moment the email comes through, e.g at 5:00pm, the Response Time deadline is 1 hour (P1) or 4 hours (P2) exactly from the dispatch time. And the completion deadline is 4 hours (P1) and 24 hours (P2) from the dispatch time.
P3 jobs are also high priority, so the deadline in all situations is 24 hours response time and 3 days completion deadline, where if we receive P3 on friday 5:00pm, the response time deadline is Saturday 5:00pm and completion deadline is on the coming Monday 5:00pm.
Only P4 and P5 are truly based on business days.

 

[jennyzhou26]

I guess the question I want to ask is what the formula/s will be now is given.
Is the formula forResponse time deadline
A1+CHOOSE(RIGHT(B1,1), TIME(1,0,0),TIME(4,0,0),TIME(24,0,0), TIME(168,0,0),1). With 168 being 7 days from original time job was assigned to us.

It doesnt look like it puts into account weekends or public holidays for P4s. Also, technically P5's dont even have any deadlines.

 

[jennyzhou26]

Also, can A1 include both date and time?, like 10:30 31/05/17.

 

[Trevor_S]

A1 can contain date and time. Within the Choose formula, the first bit removes the P to just leave the number. The subsequent bits are how long to add. If you're adding hours, you have to use the TIME(hours,minutes,seconds) formula. But if you're adding an exact number of days, you need only enter the number of days. So you can just put 1, you don't need TIME(24,0,0).

At the moment, it doesn't take account of weekends, etc. I can work on the formula to adjust for weekends, but the only way to cope with holidays would be to have a list of them somewhere on the spreadsheet.

I'll work on a revision...

 

[jennyzhou26]

Is it better to just have separate formulas for different priority work orders?
Is it okay if you write what the actual formulas for my example of p1, p2. P3, and p4 jobs are hours. Sorry about this. I need literal formulas that fits the below examples. Using the hypothetical p1, 1 hour, p2, 2 hours, p3, 3 hours is confusing since I do not have the excel knowledge to translate it into something that can work for my situation. I did not think it was going to be this complicated. Would it work better if there were cells E,F,G, etc with extra info?

E.g
Cell A1 = 10:30am 31/05/17,
Cell B1 = P1
Cell C1= Formula to calculate Response time (which should be 11:30am 31/05/17
Cell D1= Fornula to calculate completion time deadline (result should be 2:30pm 31/05/17)

And then
Cell A3= 10:30am 31/05/17
Cell B3 = P3
Cell C3 = Formula to calculate response date and time (should be 1 day from 31/05/17 which is at time 10:30am on 01/06/17, regardless of if it's weekend)
Cell D3= Formula to calculate completion date and time (should be 3 days from 31/05/17, which is time 10:30am on 03/06/17, also regardless if weekend or not)

And then
Cell A4= 10:30am 31/05/17
Cell B4 = P4
Cell C4 = Formula to calculate response date and time (should be 5 business days from 31/05/17 which is at time 10:30am on 7/06/17)
Cell D4= Formula to calculate completion date and time (should ve 10 business days from 31/05/17, which is time 10:30am on 14/06/17)