Create Due Date

[Milo6967]

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

[TABLE="width: 440"]
<tbody>[TR]
[TD]Created[/TD]
[TD]Work Hours[/TD]
[TD]Priority Code[/TD]
[TD]Due[/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2017 08:49[/TD]
[TD="align: right"]9.0[/TD]
[TD]P5 - Planned[/TD]
[TD="align: right"]08/06/2017 08:49[/TD]
[/TR]
[TR]
[TD="align: right"]29/06/2017 09:56[/TD]
[TD="align: right"]9.0[/TD]
[TD]P3 - Normal[/TD]
[TD="align: right"]04/07/2017 09:56[/TD]
[/TR]
[TR]
[TD="align: right"]29/06/2017 09:56[/TD]
[TD="align: right"]9.0[/TD]
[TD]P4 - Low[/TD]
[TD="align: right"]05/07/2017 09:56[/TD]
[/TR]
[TR]
[TD="align: right"]29/06/2017 09:56[/TD]
[TD="align: right"]9.0[/TD]
[TD]P2 - High[/TD]
[TD="align: right"]03/07/2017 09:56

[/TD]
[/TR]
</tbody>[/TABLE]
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

 

[barry houdini]

Hello Milo6967,

Welcome to MrExcel

There's a standard formula I suggest for this type of situation - see here, which can be adapted for slightly different scenarios.

In your case I suggest you use a lookup formula in D2 to get the number of business hours to add based on the priority code, i.e.

=HLOOKUP(LEFT(C2,2),{"P2","P3","P4","P5";4,8,16,24},2,0)/24

Format as [h]:mm to display as a time value like 24:00

Now in E2 you can use this formula for the due date/time

=WORKDAY(A2,CEILING((D2+MOD(A2,1)-8/24)/B2*24,1)-1)+MOD(A2,1)+D2-CEILING(MOD(A2,1)+D2-8/24,B2/24)+B2/24

Note that this uses the values in A2, B2 and D2 but also the two red highlighted 8/24s represent the work start time, so those need to be changed if that changes

This works assuming the date/time in A2 will always be within working hours

 

[Milo6967]

Hi,

Great formula but there are occasions (quite a few) that tickets are raised outside of normal business hours. Quite a lot of are automatically generated by an alerting system. Is there a way to tweak this to exclude hours outside of the core business hours Mon - Fri 0800 - 17:00 and maybe reference a holiday table so that it also excludes bank holidays.

Sorry for not including this information in my original request but I was a little shortsighted.

Regards

Milo6967

 

[barry houdini]

That can be done but the formula is a bit of a monster - switch to this version in E2 copied down

=WORKDAY(A2-1,CEILING((D2+MEDIAN(NETWORKDAYS(A2,A2,Z$2:Z$10)*MOD(A2,1),8/24,17/24)-8/24)/B2*24,1),Z$2:Z$10)+MEDIAN(NETWORKDAYS(A2,A2,Z$2:Z$10)*MOD(A2,1),8/24,17/24)+D2-CEILING(MEDIAN(NETWORKDAYS(A2,A2,Z$2:Z$10)*MOD(A2,1),8/24,17/24)+D2-8/24,B2/24)+B2/24

Where holiday dates are stored in Z2:Z10 (change as required)

Note that this version has multiple references to start and end of the workday so it might be easier to use cell references for those, e.g. if start of workday is in G1 (08:00) and end of workday is in H1 (17:00) then you can use this version:

=WORKDAY(A2-1,CEILING((D2+MEDIAN(NETWORKDAYS(A2,A2,Z$2:Z$10)*MOD(A2,1),G$1,H$1)-G$1)/B2*24,1),Z$2:Z$10)+MEDIAN(NETWORKDAYS(A2,A2,Z$2:Z$10)*MOD(A2,1),G$1,H$1)+D2-CEILING(MEDIAN(NETWORKDAYS(A2,A2,Z$2:Z$10)*MOD(A2,1),G$1,H$1)+D2-G$1,B2/24)+B2/24

 

[Milo6967]

You sir are a genius, this is exactly what I needed and as business requirements change I can tweak to suit. Thank you for saving what I have left of my hair.