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

jennyzhou26

New Member
Joined
May 27, 2017
Messages
17
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
 
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.
 
Last edited:
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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 ""?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
[TABLE="width: 396"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Logged[/TD]
[TD]Ref[/TD]
[TD]Respond[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD="align: right"]27/05/2017 22:37
[/TD]
[TD]P3[/TD]
[TD="align: right"]1/01/1900 0:00[/TD]
[TD="align: right"]3/01/1900 0:00
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top