Hi,
I have a ticketing system I run reports against and I am looking for a way to automatically populate a due date in excel by adding X number of business hours to the created date. X will depend on on a priority code.
A sample date is
<tbody>
</tbody>Due is currently calculated using this formula =IF(17-MOD(A2,1)*24>B2,A2+B2/24,WORKDAY(A2,MAX(5,INT((B2-(17-MOD(A2,1)*24))/8)))+"08:00"+MOD((B2-(17-MOD(A2,1)*24)),8)/24)
Using the above I have to manually modify the number in the formula after MAX for the number of business days to add to the created field. However when I try to modify the above to take into account business hours I fail .
What I am looking to do is have a formula that will add x business hours to the created date depending on the Priority so P2 - High would add 4 Business Hours, P3 - Normal would add 8 business hours, P4 - Low would add 16 Business hours, and P5 - Planned would add 24 business hours.
Team working hours are 08:00 to 17:00 hence the 9.0 in Work Hours.
Any assistance is greatly appreciated.
Cheers
Milo6967
I have a ticketing system I run reports against and I am looking for a way to automatically populate a due date in excel by adding X number of business hours to the created date. X will depend on on a priority code.
A sample date is
Created | Work Hours | Priority Code | Due |
01/06/2017 08:49 | 9.0 | P5 - Planned | 08/06/2017 08:49 |
29/06/2017 09:56 | 9.0 | P3 - Normal | 04/07/2017 09:56 |
29/06/2017 09:56 | 9.0 | P4 - Low | 05/07/2017 09:56 |
29/06/2017 09:56 | 9.0 | P2 - High | 03/07/2017 09:56 |
<tbody>
</tbody>
Using the above I have to manually modify the number in the formula after MAX for the number of business days to add to the created field. However when I try to modify the above to take into account business hours I fail .
What I am looking to do is have a formula that will add x business hours to the created date depending on the Priority so P2 - High would add 4 Business Hours, P3 - Normal would add 8 business hours, P4 - Low would add 16 Business hours, and P5 - Planned would add 24 business hours.
Team working hours are 08:00 to 17:00 hence the 9.0 in Work Hours.
Any assistance is greatly appreciated.
Cheers
Milo6967