Add 5 working hours (in workdays) to a specified time

A

abi_learn_vba

Board Regular
Joined
Nov 6, 2009
Messages
215
Hi

I have working hours from 7:00 to 19:00 hours. I have a date in cell A1 as "6/24/2011" time in cell as B1 "17:00". I need to add 5 hours to the this time so that my output will be on workdays as "6/27/2011 10:00 AM". Can any one suggest a formula for this?

Thanks
-Abi
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Try this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">sex 24/06/2011</TD><TD style="TEXT-ALIGN: right">17:00</TD><TD style="TEXT-ALIGN: right">05:00</TD><TD style="TEXT-ALIGN: right">seg 27/06/2011 10:00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">07:00</TD><TD style="TEXT-ALIGN: right">19:00</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">sex 17/06/2011</TD><TD style="TEXT-ALIGN: right">16:08</TD><TD style="TEXT-ALIGN: right">09:41</TD><TD style="TEXT-ALIGN: right">seg 20/06/2011 13:49</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">qua 29/06/2011</TD><TD style="TEXT-ALIGN: right">11:50</TD><TD style="TEXT-ALIGN: right">07:53</TD><TD style="TEXT-ALIGN: right">qui 30/06/2011 07:43</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">qui 16/06/2011</TD><TD style="TEXT-ALIGN: right">08:24</TD><TD style="TEXT-ALIGN: right">07:31</TD><TD style="TEXT-ALIGN: right">qui 16/06/2011 15:55</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">sex 01/07/2011</TD><TD style="TEXT-ALIGN: right">11:23</TD><TD style="TEXT-ALIGN: right">00:49</TD><TD style="TEXT-ALIGN: right">sex 01/07/2011 12:12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">seg 06/06/2011</TD><TD style="TEXT-ALIGN: right">16:20</TD><TD style="TEXT-ALIGN: right">04:49</TD><TD style="TEXT-ALIGN: right">ter 07/06/2011 09:09</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">ter 14/06/2011</TD><TD style="TEXT-ALIGN: right">15:22</TD><TD style="TEXT-ALIGN: right">11:07</TD><TD style="TEXT-ALIGN: right">qua 15/06/2011 14:29</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">qua 06/07/2011</TD><TD style="TEXT-ALIGN: right">10:51</TD><TD style="TEXT-ALIGN: right">03:15</TD><TD style="TEXT-ALIGN: right">qua 06/07/2011 14:06</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">sex 01/07/2011</TD><TD style="TEXT-ALIGN: right">11:50</TD><TD style="TEXT-ALIGN: right">10:50</TD><TD style="TEXT-ALIGN: right">seg 04/07/2011 10:40</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">qui 30/06/2011</TD><TD style="TEXT-ALIGN: right">16:33</TD><TD style="TEXT-ALIGN: right">00:23</TD><TD style="TEXT-ALIGN: right">qui 30/06/2011 16:56</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">qua 08/06/2011</TD><TD style="TEXT-ALIGN: right">17:55</TD><TD style="TEXT-ALIGN: right">01:27</TD><TD style="TEXT-ALIGN: right">qui 09/06/2011 07:22</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>***************</TD><TD>******</TD><TD>******</TD><TD>***************</TD><TD>******</TD><TD>******</TD><TD>******</TD></TR></TBODY></TABLE>
​


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D1</TH><TD style="TEXT-ALIGN: left">=IF((B1+C1)>$G$1,A1+IF(WEEKDAY(A1)=6,3,1)+$F$1+C1-($G$1-B1),A1+B1+C1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz
 
Last edited:
Upvote 0
This was my go at it...
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">6/24/2011</td><td style="text-align: right;;">17:00</td><td style="text-align: right;;">6/27/2011 10:00</td></tr></tbody></table><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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">B1>14/24,WORKDAY(<font color="Red">A1,1,Holidays</font>)+B1-7/24,A1+B1+5/24</font>)</td></tr></tbody></table></td></tr></table><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>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Holidays</th><td style="text-align:left">=Sheet1!$D$1:$D$10</td></tr></tbody></table></td></tr></table><br />

Holidays is a named range with the Holiday dates
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Locked
  • Question Question
Trying to input time calculation so it does not change to 24 hour format
Replies
3
Views
155
Peter_SSs
Peter_SSs
J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
723
felixstraube
F
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
84
cooleomter
C
D
  • Question Question
Can time frames/windows be used as a condition for any functions?
Replies
4
Views
124
dallas2699
D
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
651
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,224,592
Messages
6,179,790
Members
452,942
Latest member
VijayNewtoExcel

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
Back
Top