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]

Here's some amended formulas. You need to add a new sheet to your workbook, call it Holidays, and list all the public/state holidays (including the year) in column A of that sheet.

In your original sheet, with the start date in A1 and work order type in B1:
Response deadline:
=CHOOSE(RIGHT(B1,1),A1+TIME(1,0,0),A1+TIME(4,0,0),1,WORKDAY(A1,5,Holidays!A:A)+(A1-INT(A1)),"")
Completion deadline:
=CHOOSE(RIGHT(B1,1),A1+TIME(4,0,0),1,3,WORKDAY(A1,10,Holidays!A:A)+(A1-INT(A1)),"")

The workday function only returns dates, which is why I've added the time element (A1-INT(A1)) back on.

If the public/state holidays don't work as you expect, it may be down to date formats in the new Holidays sheet. In which case, enter dates in that sheet using the DATE function, e.g. =DATE(2017,12,25) for Christmas Day.

 

[jennyzhou26]

Wow, Thank you. So the above formulas are for P1, right?
What about for P4 where its based on days and hours?
And what goes into ""?

 

[Trevor_S]

They cover P1-P5.

You need the Holidays sheet, so the formula knows for P4 when it needs to adjust for a public/state holiday.

The "" covers P5, where you said that there were no deadlines, so the dates are left blank.

 

[jennyzhou26]

I was looking from my phone, just saw the rest of the formula. Thankyou so very much!!!!

 

[Trevor_S]

Here's a sample spreadsheet (link below) in case it helps. Add more dates/times and work order types in columns A and B of the main sheet, copy down the formulas in columns C and D, and add more public holidays to the list on the Holidays sheet ... hopefully it will do what you need!
https://drive.google.com/file/d/0B_dlXxlE0XVBR0xxM2RCS0xkSm8/view?usp=drivesdk

 

[jennyzhou26]

Hi,

I tried out the formula and it only works for P1 and P2, the moment it hits that 1 day mark or 5 work days the dates go crazy. I changed the priority to P3 and P4 and it comes up with dates that do not make sense. Like I changed the Priority to P3 on your spreadsheet and it showed up as

Logged	Ref	Respond	Complete
27/05/2017 22:37	P3	1/01/1900 0:00	3/01/1900 0:00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

And although the P4 thing worked for this spreadsheet, most of the time the dates for P4 are al over the place, like year becomes 2253 if I change the date and time a bit.

 

[jennyzhou26]

For some reason when I did the spreadsheet it didn't work: see what happened when I typed in P4
https://docs.google.com/spreadsheet...-FmwScp49KtADKHre3DKmcxe0/edit#gid=1037589582

 

[Trevor_S]

Ive solved the P3 (and P2 completed date) issue. The formula was setting the date to 1 or 3, rather than adding that number to the current date. Updated spreadsheet here: https://drive.google.com/file/d/0B_dlXxlE0XVBYUdIdTgxV3BmUlk/view?usp=drivesdk

Unfortunately its not letting me open your google drive link - can you give me an example please of a date and time that isn't working? The google drive screen offered an option to request permission to see your spreadsheet, so I've clicked on that.

 

[jennyzhou26]

29/5/17 11:11AM P4 shows up as 4/2/52 11:11am on my sheet

 

[jennyzhou26]

Dont know why date showed up as 5/29/17,