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

Mangeshkhati

New Member
Joined
Jun 30, 2015
Messages
10
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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
BCDEF
501-Jul 07:0002-Jul 05:0002-Jul 05:00
602-Jul 12:0003-Jul 05:00
703-Jul 00:0004-Jul 05:00
804-Jul 07:0006-Jul 05:00
905-Jul 18:3006-Jul 05:00
1006-Jul 00:0007-Jul 05:00
1107-Jul 10:0008-Jul 05:00
1208-Jul 00:0009-Jul 05:00

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

Worksheet Formulas
CellFormula
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

New Member
Joined
Jun 30, 2015
Messages
10
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
BCDEF
501-Jul 07:0002-Jul 05:0002-Jul 05:00
602-Jul 12:0003-Jul 05:00
703-Jul 00:0004-Jul 05:00
804-Jul 07:0006-Jul 05:00
905-Jul 18:3006-Jul 05:00
1006-Jul 00:0007-Jul 05:00
1107-Jul 10:0008-Jul 05:00
1208-Jul 00:0009-Jul 05:00

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Ah, sorry. Didn't look at the detail. I thought it was from 5:00 AM

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">01/07/2015 07:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">01/07/2015 17:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">03/07/2015 17:00</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">02/07/2015 12:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">02/07/2015 17:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">03/07/2015 00:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">03/07/2015 00:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">04/07/2015 07:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/07/2015 17:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">05/07/2015 18:30</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/07/2015 17:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/07/2015 00:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/07/2015 00:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">07/07/2015 10:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">07/07/2015 17:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">08/07/2015 00:00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">08/07/2015 00:00</td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F5</th><td style="text-align:left">=IF(<font color="Blue">WEEKDAY(<font color="Red">NOW(<font color="Green"></font>),2</font>)=6,INT(<font color="Red">NOW(<font color="Green"></font>)</font>)+2+17/24,IF(<font color="Red">WEEKDAY(<font color="Green">NOW(<font color="Purple"></font>),2</font>)=7,INT(<font color="Green">NOW(<font color="Purple"></font>)</font>)+1+17/24,IF(<font color="Green">OR(<font color="Purple">(<font color="Teal">NOW(<font color="#FF00FF"></font>)-INT(<font color="#FF00FF">NOW(<font color="Navy"></font>)</font>)</font>)<5/24,(<font color="Teal">NOW(<font color="#FF00FF"></font>)-INT(<font color="#FF00FF">NOW(<font color="Navy"></font>)</font>)</font>)>17/24</font>),NOW(<font color="Purple"></font>),INT(<font color="Purple">NOW(<font color="Teal"></font>)</font>)+17/24</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D5</th><td style="text-align:left">=IF(<font color="Blue">WEEKDAY(<font color="Red">B5,2</font>)=6,INT(<font color="Red">B5</font>)+2+17/24,IF(<font color="Red">WEEKDAY(<font color="Green">B5,2</font>)=7,INT(<font color="Green">B5</font>)+1+17/24,IF(<font color="Green">OR(<font color="Purple">(<font color="Teal">B5-INT(<font color="#FF00FF">B5</font>)</font>)<5/24,(<font color="Teal">B5-INT(<font color="#FF00FF">B5</font>)</font>)>17/24</font>),B5,INT(<font color="Purple">B5</font>)+17/24</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Mangeshkhati

New Member
Joined
Jun 30, 2015
Messages
10

ADVERTISEMENT

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

Excel 2010
BCDEF
501/07/2015 07:0001/07/2015 17:0003/07/2015 17:00
602/07/2015 12:0002/07/2015 17:00
703/07/2015 00:0003/07/2015 00:00
804/07/2015 07:0006/07/2015 17:00
905/07/2015 18:3006/07/2015 17:00
1006/07/2015 00:0006/07/2015 00:00
1107/07/2015 10:0007/07/2015 17:00
1208/07/2015 00:0008/07/2015 00:00

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

Worksheet Formulas
CellFormula
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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")
 
Last edited:

Mangeshkhati

New Member
Joined
Jun 30, 2015
Messages
10

ADVERTISEMENT

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

New Member
Joined
Jun 30, 2015
Messages
10
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

New Member
Joined
Jun 30, 2015
Messages
10
Need to get the deadline after adding 5 working days. Working day starts from 5 PM to 5:30 AM.
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Please explain in more detail. Exactly how is the deadline calculated. Give some examples please
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

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
Top