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

M

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

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>
 
M

Mangeshkhati

New Member
Joined
Jun 30, 2015
Messages
10
sijpie said:
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>
Click to expand...
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

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 />
 
M

Mangeshkhati

New Member
Joined
Jun 30, 2015
Messages
10

ADVERTISEMENT

sijpie said:
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>
Click to expand...
Thank you so much, its working fine now.
 
barry houdini

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Mangeshkhati said:
The business day starts at 5:00:00 PM IST and ends at 5:30:00 AM. Saturday and Sunday are holiday.
Click to expand...

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:
M

Mangeshkhati

New Member
Joined
Jun 30, 2015
Messages
10

ADVERTISEMENT

barry houdini said:
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")
Click to expand...


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

Mangeshkhati

New Member
Joined
Jun 30, 2015
Messages
10
Mangeshkhati said:
Yes Barry, Thank you so much. I didn't realize it while validating the scenario.
Click to expand...

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.
 
M

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

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
 
You must log in or register to reply here.

Similar threads

B
  • Question
Get amount of time inside and outside of two overlapping time periods
Replies
2
Views
153
brux2dc
B
L
  • Question
Networkdays help - calculating business hours only
Replies
0
Views
302
Li_zzza
L
P
  • Question
Excel formula to calculate elapse days/hours/mins between two dates
Replies
4
Views
359
pinkyviolet08
P
S
  • Solved
Timesheet Calculation
Replies
8
Views
258
A Durfani
A Durfani
S
  • Question
Find and insert
Replies
0
Views
72
stretchr
S

Watch MrExcel Video

Learn Excel - Pivot From Many Sheets: Podcast #1331

Forum statistics

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

Share this page

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