Date/Time variance formula that recognizes business days and skips weekends/holidays

E

Erica2261

New Member
Joined
Apr 17, 2008
Messages
31
I am using Excel 2003 and I trying to create a tracking log to determine if tasks are being completed within the 48 hour standard my business has established. I have created the following chart which identifies in columns E & F the date/time a request was originated, and in columns G & H the time the request was completed in the system by an associate. Column I correctly calculates the hours, minutes (and seconds which I really don't need) that it took for the action to be completed. Column J states if the corporate standard was met (completed in <= 48 hours).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
My problem is that my formulas do not take into account business days (I don't want to penalize someone for not working on the weekend). To complicate things further, I would ideally also like to skip stock market holidays when the company is closed.<o:p></o:p>
<o:p></o:p>
My formula in cell I3 is: =(G3+H3)-(E3+F3)<o:p></o:p>
My formula in column J3 is: =IF(I3>2,"no","yes") <o:p></o:p>
My cells in column I are formatted as: 37:30:55<o:p></o:p>
<o:p></o:p>

<TABLE class=MsoNormalTable style="WIDTH: 501.95pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0pt 0pt 0pt 0pt" cellSpacing=0 cellPadding=0 width=669 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: windowtext 1pt solid; WIDTH: 27.25pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt" width=36>
A<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=96>
B<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=96>
C<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
D<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
E<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
F<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
G<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 51.05pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=68>
H<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45.65pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=61>
I<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=72>
J<o:p></o:p>

​
</TD></TR><TR style="HEIGHT: 25.5pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: windowtext 1pt solid; WIDTH: 27.25pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=36>
Doc<o:p></o:p>


#<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=96>
Document Name<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=96>
Document Type<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
Submitter<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
Date<o:p></o:p>


Submitted<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
Time<o:p></o:p>


Submitted<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
Date<o:p></o:p>


Completed<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 51.05pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=68>
Time<o:p></o:p>


Completed<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45.65pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=61>
Time<o:p></o:p>


Variance<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: silver; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=72>
Meets<o:p></o:p>

Standards?<o:p></o:p>

​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: windowtext 1pt solid; WIDTH: 27.25pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=36>
483<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=96>
Document #1<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=96>
Policy<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
Hill<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
08/21/08<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
5:00<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
08/22/08<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 51.05pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=68>
6:34<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45.65pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=61>
25:34:00<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=72>
yes<o:p></o:p>

​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: windowtext 1pt solid; WIDTH: 27.25pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=36>
485<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=96>
Document #2<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=96>
Procedure<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
Hill<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
08/21/08<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
17:47<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=60>
08/23/08<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 51.05pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=68>
18:48<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; BACKGROUND: #99ccff; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 45.65pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=61>
49:01:00<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0pt; PADDING-BOTTOM: 0pt; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=72>
no<o:p></o:p>​


</TD></TR></TBODY></TABLE>​
<o:p></o:p>
Can anyone suggest a way to change my formulas so that only business days are counted in column I (and not weekends/market holidays).<o:p></o:p>
<o:p></o:p>
Thank you!<o:p></o:p>
Erica<o:p></o:p>
 
Sort by date Sort by votes
Well, I can get you started, but to account for holidays you'd need to get a little trickier than this. The following will return the hours disregarding weekends:

=(G3-E3-SUM(--(WEEKDAY(DATE(YEAR(E3),MONTH(E3),DAY(E3)+ROW(INDIRECT("1:"&G3-E3+1))-1),2)>5)))*24-(F3*24)+(H3*24)

You have to use Ctrl+Shift+Enter to enter this formula, not just Enter. Please.

You'd use a similar array-generating technique to get the holidays from a list, unless someone can think of something better.
 
Upvote 0
Try this formula in I3

=NETWORKDAYS(E3,G3,holidays)+NETWORKDAYS(G3,G3,holidays)*(H3-1)-NETWORKDAYS(E3,E3,holidays)*F3

where holidays is a named range containing holidays dates

Note: NETWORKDAYS is an Analysis ToolPak add-in function
 
Upvote 0
Barry -

Thank you!
Your formula worked perfectly.
Your help is greatly appreciated.

(my thanks to Iliace as well) -

Erica
 
Upvote 0
Well, you can do it without Analysis ToolPak functions but in this case, assuming that holidays need to be excluded and that start time and date can be any time, even on a holiday or weekend, the formula's a little longer than I like, hence my preference for NETWORKDAYS....

...but you could get the same result like this

=SUM(INT((WEEKDAY(E3-{2,3,4,5,6})+G3-E3)/7))-SUMPRODUCT(--(holidays>=E3),--(holidays<=G3),--(WEEKDAY(holidays,2)<6))+IF(COUNTIF(holidays,G3)+(WEEKDAY(G3,2)>5)=0,H3-1)-IF(COUNTIF(holidays,E3)+(WEEKDAY(E3,2)>5)=0,F3)

The formula can be shortened if either start or end date can't be at weekends or on holidays.....but I note that Erica's example shows a Saturday end date......
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
950
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
3K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

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