Updating start times

[Leonidas75]

I am trying to create a formula where hours of business are from Monday 8am through to Friday 11PM uninterrupted. If i receive a ticket inside these hours my target response time is time received + 24 hours

What i am trying to calculate is if i receive a ticket outside of hours (say Friday 11.30pm). the ticket start time should begin on Monday 8am with response target Tuesday 8am (Public Holidays should be included).

If i receive a ticket on Friday 10PM then i have until Monday 10PM to respond (or Tuesday 10PM if monday PH).

so i need to show 2 columns - start time and target time.

 

[Crystalyzer]

REVISED: THIS HAS AN ERROR THAT I NEED TO WORKOUT

I set up the worksheet below and faked that this coming Monday is a holiday to illustrate taking holidays into account.

Book5
	A	B	C	D	E
1		8:00 AM			Holidays
2		11:00 PM			1/1/2021
3					7/4/2021
4					8/16/2021
5	Ticket Rec'd Time	Start Time	Target Time
6	8/13/2021 23:59	8/13/2021 8:00	8/17/2021 8:00
7
Sheet1

Cell Formulas
Range	Formula
B6	B6	=IF(WORKDAY(INT(A6)-1,1,Holidays)<>INT(A6),WORKDAY(INT(A6)-1,1,Holidays),INT(A6))+IF(AND(BusStartTime<=(A6-INT(A6)),(A6-INT(A6))<=BusEndTime),(A6-INT(A6)),BusStartTime)
C6	C6	=WORKDAY(INT(B6),1,Holidays)+(B6-INT(B6))

Named Ranges
Name	Refers To	Cells
BusEndTime	=Sheet1!$B$2	B6
BusStartTime	=Sheet1!$B$1	B6
Holidays	=Sheet1!$E$2:$E$4	B6:C6

 

[Crystalyzer]

Ok, I worked it out

Book5
	A	B	C	D	E
1		8:00 AM			Holidays
2		11:00 PM			1/1/2021
3					7/4/2021
4					8/16/2021
5	Ticket Rec'd Time	Start Time	Target Time
6	8/13/2021 23:30	8/17/2021 8:00	8/18/2021 8:00
7
Sheet1

Cell Formulas
Range	Formula
B6	B6	=IF(WORKDAY(INT(A6)-1,1,Holidays)<>INT(A6),WORKDAY(INT(A6)-1,1,Holidays),IF((A6-INT(A6))>BusEndTime,WORKDAY(INT(A6),1,Holidays),INT(A6)))+IF(AND(BusStartTime<=(A6-INT(A6)),(A6-INT(A6))<=BusEndTime),(A6-INT(A6)),BusStartTime)
C6	C6	=WORKDAY(INT(B6),1,Holidays)+(B6-INT(B6))

Named Ranges
Name	Refers To	Cells
BusEndTime	=Sheet1!$B$2	B6
BusStartTime	=Sheet1!$B$1	B6
Holidays	=Sheet1!$E$2:$E$4	B6:C6

 

[Leonidas75]

Thanks for that and appreciate the quick turnaround. I have tried it and it works however some components remain outstanding. For example:

Since it is 24/7 between Monday 8am start to Friday 11pm finish there should not be a reset - just + 24 hours. At present, tickets that are arriving before 8am on a weekday are resetting to a start time of 8am.

Eg:
If i receive a ticket on Wednesday 02:45, then i have until Thursday 02:45
If i receive a ticket on Tuesday 14:30, then i have until Wednesday 14:30
If i receive a ticket on Friday 23:30, then i have until Tuesday 08:00 (Monday 08:00 is the effective start time)
If i receive a ticket on Saturday 04:30, then i have until Tuesday 08:00 (Monday 08:00 is the effective start time)
If i receive a ticket on Sunday 15:30, then i have until Tuesday 08:00 (Monday 08:00 is the effective start time).

Is it possible to rather than have an extra column, go directly from the received ticket time to start time to target time (ie. remove the start time column so i am left with just the received time column and target time).

 

[Crystalyzer]

Thanks for that and appreciate the quick turnaround. I have tried it and it works however some components remain outstanding. For example:

Since it is 24/7 between Monday 8am start to Friday 11pm finish there should not be a reset - just + 24 hours. At present, tickets that are arriving before 8am on a weekday are resetting to a start time of 8am.

Eg:
If i receive a ticket on Wednesday 02:45, then i have until Thursday 02:45
If i receive a ticket on Tuesday 14:30, then i have until Wednesday 14:30
If i receive a ticket on Friday 23:30, then i have until Tuesday 08:00 (Monday 08:00 is the effective start time)
If i receive a ticket on Saturday 04:30, then i have until Tuesday 08:00 (Monday 08:00 is the effective start time)
If i receive a ticket on Sunday 15:30, then i have until Tuesday 08:00 (Monday 08:00 is the effective start time).

Is it possible to rather than have an extra column, go directly from the received ticket time to start time to target time (ie. remove the start time column so i am left with just the received time column and target time).

Ok, I will look at this. Yes I can make it only two columns "Ticket Rec'd" and "Target"

 

[Crystalyzer]

REMOVED

 

[Crystalyzer]

Ok, I posted late last night but realized that I had some issues I needed to correct. Please check the yellow highlighted cells as I wasn't sure which way you wanted to got there. The scenario is that a ticket is received either on Thursday after 11:00 PM OR on Friday before 8:00 AM which would cause the Target Time to calculate to a day and time that was NOT in the business hours. In both cases I chose to make the target time calculate to Monday 8:00 AM. If that assumption is correct, I think this solves all cases. Please don't hesitate to reach out with ANY questions.

Leonidas75 20210813.xlsx
	A	B	C	D
1	Ticket Rec'd Time	Target Time		Holidays
2	Mon 08/09/21 07:59 AM	Tue 08/10/21 08:00 AM		Fri 01/01/21
3	Mon 08/09/21 08:00 AM	Tue 08/10/21 08:00 AM		Mon 01/18/21
4	Mon 08/09/21 08:01 AM	Tue 08/10/21 08:01 AM		Mon 02/15/21
5	Tue 08/10/21 10:00 AM	Wed 08/11/21 10:00 AM		Mon 05/31/21
6	Tue 08/10/21 11:59 PM	Wed 08/11/21 11:59 PM		Sun 07/04/21
7	Wed 08/11/21 07:30 AM	Thu 08/12/21 07:30 AM		Mon 07/05/21
8	Wed 08/11/21 04:05 PM	Thu 08/12/21 04:05 PM		Mon 09/06/21
9	Thu 08/12/21 12:01 PM	Fri 08/13/21 12:01 PM		Mon 10/11/21
10	Thu 08/12/21 11:00 AM	Fri 08/13/21 11:00 AM		Thu 11/25/21
11	Thu 08/12/21 11:00 PM	Mon 08/16/21 08:00 AM		Sat 12/25/21
12	Thu 08/12/21 11:30 PM	Mon 08/16/21 08:00 AM
13	Fri 08/13/21 05:00 AM	Mon 08/16/21 08:00 AM
14	Fri 08/13/21 05:06 PM	Mon 08/16/21 05:06 PM
15	Fri 08/13/21 11:01 PM	Tue 08/17/21 08:00 AM
16	Sat 08/14/21 09:00 AM	Tue 08/17/21 08:00 AM
17	Sun 08/15/21 04:20 PM	Tue 08/17/21 08:00 AM
18	Named Formulas that must be created. You can copy these into the Name Manager directly if your first row of "Ticket Rec'd Time" is = A2. If it isn't remember to use the first cell of the "Ticket Rec'd Time" and anchor the column ONLY.
19	AftBusHrsTgt	=WORKDAY(INT(Sheet1!$A19),IF(WEEKDAY(INT(Sheet1!$A19),15)<4,2,1),Holidays)+BusStartTime
20	BusEndTime	=0.95833333
21	BusHrsTgt	=WORKDAY(INT(Sheet1!$A21),1,Holidays)+TktTimeRcd
22	BusStartTime	=0.33333333
23	Holidays	=Sheet1!$D$2:$D$11
24	TktDTFriPreBus	=AND(WEEKDAY(Sheet1!$A24,15)=1,TktTimeRcd<BusStartTime)
25	TktDTThuAftBusFri	=AND(WEEKDAY(Sheet1!$A25,14)=1,TktTimeRcd>BusEndTime)
26	TktRcdAftBusHrs	=OR(WORKDAY(INT(Sheet1!$A26)-1,1,Holidays)<>INT(Sheet1!$A26),WEEKDAY(Sheet1!$A26,16)<3,AND(WEEKDAY(Sheet1!$A26,16)=3,ROUND(Sheet1!$A26-INT(Sheet1!$A26),8)<BusStartTime),AND(WEEKDAY(Sheet1!$A26,2)=5,ROUND(Sheet1!$A26-INT(Sheet1!$A26),8)>BusEndTime))
27	TktRcdThuAftFriPreBusHrs	=WORKDAY(INT(Sheet1!$A27),IF(WEEKDAY(INT(Sheet1!$A27), 14)<2,2,1),Holidays)+BusStartTime
28	TktTimeRcd	=Sheet1!$A28-INT(Sheet1!$A28)
Sheet1

Cell Formulas
Range	Formula
B2:B17	B2	=IF(OR(TktDTThuAftBusFri,TktDTFriPreBus),TktRcdThuAftFriPreBusHrs,IF(TktRcdAftBusHrs,AftBusHrsTgt,BusHrsTgt))

Named Ranges
Name	Refers To	Cells
Holidays	=Sheet1!$D$2:$D$11	B2:B17

 

[Leonidas75]

Thanks for that, i will test this out during the day. Will this cover other scenarios such as

If ticket is received on say Tuesday at 02:35am the target time will be Wednesday 02:35am?

 

[Leonidas75]

Forgot to answer your highlighted rows - they would actually be +24 hours vs Monday 8am. Even though the Friday would have a Saturday target we should still be able to respond to tickets within the work day

 

[Crystalyzer]

If ticket is received on say Tuesday at 02:35am the target time will be Wednesday 02:35am?

Yes see data above.

they would actually be +24 hours vs Monday 8am. Even though the Friday would have a Saturday target we should still be able to respond to tickets within the work day

So if a ticket is received

Thu 11:30 PM the target time should be Fri 11:30 PM?
Fri 7:00 AM the target time should be Sat 7:00 AM?

Even though these targets are outside business hours?

would it make more sense to make the Target time Fri 11:00 PM?