How to set date and time to next business date and time

[Mangeshkhati]

Hi All,
Is there any formula which will populate the next business day and time if i get a ticket during non business hours?
The business day starts at 5:00:00 PM IST and ends at 5:30:00 AM. Saturday and Sunday are holiday.
Example:
If i get a ticket on 26th June 4:30:15 PM, in next column the open date should be populated as 26th June 5:00:00 PM.


Regards,
Mangesh

 

[sijpie]

the two tables on the left show dates & time ticet received, and date time to be assigned. The formula in column F would be what you'd use for a ticket coming in now.

Excel 2010

	B	C	D	E	F
5	01-Jul 07:00		02-Jul 05:00		02-Jul 05:00
6	02-Jul 12:00		03-Jul 05:00
7	03-Jul 00:00		04-Jul 05:00
8	04-Jul 07:00		06-Jul 05:00
9	05-Jul 18:30		06-Jul 05:00
10	06-Jul 00:00		07-Jul 05:00
11	07-Jul 10:00		08-Jul 05:00
12	08-Jul 00:00		09-Jul 05:00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F5 =IF(WEEKDAY(NOW(),2)=6,INT(NOW())+2+5/24,INT(NOW())+1+5/24) D5 =IF(WEEKDAY(B5,2)=6,INT(B5)+2+5/24,INT(B5)+1+5/24) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Mangeshkhati]

the two tables on the left show dates & time ticet received, and date time to be assigned. The formula in column F would be what you'd use for a ticket coming in now.

Excel 2010

	B	C	D	E	F
5	01-Jul 07:00		02-Jul 05:00		02-Jul 05:00
6	02-Jul 12:00		03-Jul 05:00
7	03-Jul 00:00		04-Jul 05:00
8	04-Jul 07:00		06-Jul 05:00
9	05-Jul 18:30		06-Jul 05:00
10	06-Jul 00:00		07-Jul 05:00
11	07-Jul 10:00		08-Jul 05:00
12	08-Jul 00:00		09-Jul 05:00

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F5 =IF(WEEKDAY(NOW(),2)=6,INT(NOW())+2+5/24,INT(NOW())+1+5/24) D5 =IF(WEEKDAY(B5,2)=6,INT(B5)+2+5/24,INT(B5)+1+5/24) <tbody> </tbody>

<tbody>
</tbody>

Thanks for your help, however it is not providing a correct result.
I did not get significance of formula in column F.
I have a ticket came at 7/3/2015 6:00:00 PM, the formula in D column is giving me result as 7/4/2015 5:00:00 AM.
My business hours are 5:00 PM to 5:30 AM and the ticket had come at 6 PM, so the formula should have populated the same day and time.

 

[sijpie]

Ah, sorry. Didn't look at the detail. I thought it was from 5:00 AM

Excel 2010
	B	C	D	E	F
5	01/07/2015 07:00		01/07/2015 17:00		03/07/2015 17:00
6	02/07/2015 12:00		02/07/2015 17:00
7	03/07/2015 00:00		03/07/2015 00:00
8	04/07/2015 07:00		06/07/2015 17:00
9	05/07/2015 18:30		06/07/2015 17:00
10	06/07/2015 00:00		06/07/2015 00:00
11	07/07/2015 10:00		07/07/2015 17:00
12	08/07/2015 00:00		08/07/2015 00:00
Sheet5

Cell Formulas
Range	Formula
F5	=IF(WEEKDAY(NOW(),2)=6,INT(NOW())+2+17/24,IF(WEEKDAY(NOW(),2)=7,INT(NOW())+1+17/24,IF(OR((NOW()-INT(NOW()))<5/24,(NOW()-INT(NOW()))>17/24),NOW(),INT(NOW())+17/24)))
D5	=IF(WEEKDAY(B5,2)=6,INT(B5)+2+17/24,IF(WEEKDAY(B5,2)=7,INT(B5)+1+17/24,IF(OR((B5-INT(B5))<5/24,(B5-INT(B5))>17/24),B5,INT(B5)+17/24)))

 

[Mangeshkhati]

Ah, sorry. Didn't look at the detail. I thought it was from 5:00 AM

Excel 2010

	B	C	D	E	F
5	01/07/2015 07:00		01/07/2015 17:00		03/07/2015 17:00
6	02/07/2015 12:00		02/07/2015 17:00
7	03/07/2015 00:00		03/07/2015 00:00
8	04/07/2015 07:00		06/07/2015 17:00
9	05/07/2015 18:30		06/07/2015 17:00
10	06/07/2015 00:00		06/07/2015 00:00
11	07/07/2015 10:00		07/07/2015 17:00
12	08/07/2015 00:00		08/07/2015 00:00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula F5 =IF(WEEKDAY(NOW(),2)=6,INT(NOW())+2+17/24,IF(WEEKDAY(NOW(),2)=7,INT(NOW())+1+17/24,IF(OR((NOW()-INT(NOW()))<5/24,(NOW()-INT(NOW()))>17/24),NOW(),INT(NOW())+17/24))) D5 =IF(WEEKDAY(B5,2)=6,INT(B5)+2+17/24,IF(WEEKDAY(B5,2)=7,INT(B5)+1+17/24,IF(OR((B5-INT(B5))<5/24,(B5-INT(B5))>17/24),B5,INT(B5)+17/24))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you so much, its working fine now.

 

[barry houdini]

The business day starts at 5:00:00 PM IST and ends at 5:30:00 AM. Saturday and Sunday are holiday.

So where does the weekend start and end? Presumably you work from Friday at 5 PM until Saturday at 5:30 AM and then there is a break until Monday at 5 PM? If so then if you receive a ticket at 04:15 on Saturday AM that's actually within the working hours and the formula should return the same time and date.

For that try this formula assuming ticket received time/date in A2

=IF(AND(WEEKDAY(A2-"5:30",2)<6,MOD(A2+"7:00",1)<="12:30"+0),A2,WORKDAY(A2-1,1)+"17:00")

 

[Mangeshkhati]

So where does the weekend start and end? Presumably you work from Friday at 5 PM until Saturday at 5:30 AM and then there is a break until Monday at 5 PM? If so then if you receive a ticket at 04:15 on Saturday AM that's actually within the working hours and the formula should return the same time and date.

For that try this formula assuming ticket received time/date in A2

=IF(AND(WEEKDAY(A2-"5:30",2)<6,MOD(A2+"7:00",1)<="12:30"+0),A2,WORKDAY(A2-1,1)+"17:00")

Yes Barry, Thank you so much. I didn't realize it while validating the scenario.

 

[Mangeshkhati]

Yes Barry, Thank you so much. I didn't realize it while validating the scenario.

I have come across one more challenge. With the above formula i am able to handle business start time however i need to arrive at the deadline based on business above business hours. Is it possible?

Appreciating your help.

 

[Mangeshkhati]

Need to get the deadline after adding 5 working days. Working day starts from 5 PM to 5:30 AM.

 

[sijpie]

Please explain in more detail. Exactly how is the deadline calculated. Give some examples please