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

[Erica2261]

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-comfficeffice" /><o></o>
<o></o>
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></o>
<o></o>
My formula in cell I3 is: =(G3+H3)-(E3+F3)<o></o>
My formula in column J3 is: =IF(I3>2,"no","yes") <o></o>
My cells in column I are formatted as: 37:30:55<o></o>
<o></o>

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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


#<o></o>

​

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

​

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

​

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

​

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


Submitted<o></o>

​

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


Submitted<o></o>

​

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


Completed<o></o>

​

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


Completed<o></o>

​

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


Variance<o></o>

​

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

Standards?<o></o>

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

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

​

</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></o>​

</TD></TR></TBODY></TABLE>​

<o></o>
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></o>
<o></o>
Thank you!<o></o>
Erica<o></o>

 

[iliace]

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.

 

[barry houdini]

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

 

[Erica2261]

Barry -

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

(my thanks to Iliace as well) -

Erica

 

[iliace]

Note: NETWORKDAYS is an Analysis ToolPak add-in function

That's what I was trying to avoid.

 

[barry houdini]

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