Networkdays formula with Dynamic range

A

Angel88

Board Regular
Joined
Oct 5, 2009
Messages
51
Hi,

Below is a data where i apply the networkdays formula [column F "Time Diff" & G "Time-Diff (Holidays)"].The reason i have 2 cols of networkdays is because i have 2 criterias given. One without Holidays and the other includes holidays in the formula.
I have a seperate file which shows Holidays in each country, using this file i have retrieved all the countries that are in the Holiday list using Vlookup formula (Column H). Then I filter for all #N/A's and add the networkdays formula without holidays; which is:
=IF(C2<>"Closed","N/A",NETWORKDAYS(D2,E2)-1-MOD(D2,1)+MOD(E2,1))
C2 = Status
D2 = Start date
E2 = End date

I then filter for all the countries found in the Holiday list and apply another Networkdays formula which shows as;
=IF(C2<>"Closed","N/A",NETWORKDAYS(D2,E2,OFFSET('[Holiday Calendar.xls]2010'!$B$1,MATCH(A2,'[Holiday Calendar.xls]2010'!$B:$B,0)-1,1,COUNTIF('[Holiday Calendar.xls]2010'!$B:$B,A2),1))-1-MOD(D2,1)+MOD(E2,1))
C2 = Status
D2 = Start date
E2 = End date
A2 = Country name

[In the Holiday Calender.xls, I have Col A as the Region Names and Col B as Country Names and Col C has the dates.]

This problem is that i have to do this for many files in the same way and i am trying to automate it, but i have to always add Vlookup and then filter to add this formula and also i have to always keep the Holiday xls file open for this formula to work.
Is there a way for me to combine these 2 formulas ?
or would you suggest a different method ?

Please help me to figure out a way to reduce this manual effort. Thank you..

<TABLE style="WIDTH: 511pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=681 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #272727" width=64 height=20>Country Name</TD><TD class=xl65 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #272727" width=64>Region Name</TD><TD class=xl65 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #272727" width=64>Status</TD><TD class=xl65 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 94pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #272727" width=125>Start date</TD><TD class=xl69 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 91pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #00b0f0" width=121>End date</TD><TD class=xl70 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b050" width=64>Time Diff</TD><TD class=xl70 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: white; WIDTH: 86pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #00b050" width=115>Time-Diff (Holidays)</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #92d050" width=64>Vlookup</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Germany</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/4/2010 06:43:23 AM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/4/2010 07:57:27 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.05</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Germany</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">12/21/2009 03:47:17 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/4/2010 07:57:14 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>9.67</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/7/2010 01:04:39 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/7/2010 06:08:52 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.21</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Spain</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/5/2010 09:52:20 AM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/5/2010 10:53:15 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.04</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/7/2010 10:52:24 AM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/7/2010 03:29:07 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.19</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/13/2010 12:17:09 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/13/2010 12:26:52 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.01</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Switzerland</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/8/2010 11:15:07 AM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/12/2010 01:51:44 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2.11</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Switzerland</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/8/2010 10:45:14 AM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/8/2010 01:35:24 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.12</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Germany</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/13/2010 02:34:22 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/14/2010 01:29:13 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.95</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Italy</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/12/2010 01:46:24 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/12/2010 02:12:17 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.02</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>France</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/27/2010 01:51:56 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/28/2010 10:17:16 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.85</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>France</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/26/2010 01:15:12 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/26/2010 03:11:21 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.08</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Ukraine</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/25/2010 12:59:53 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/26/2010 10:36:18 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.90</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>United Kingdom</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/25/2010 04:31:19 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/26/2010 08:00:07 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.65</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>United Kingdom</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/25/2010 11:19:43 AM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/25/2010 12:24:33 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.05</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Ukraine</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/26/2010 11:20:13 AM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/26/2010 02:51:00 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.15</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Italy</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EMEA</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/28/2010 08:26:58 AM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/29/2010 08:06:01 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.99</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>United States</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Americas</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/4/2010 05:45:52 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/7/2010 05:44:56 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2.50</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2.50</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">United States</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Mexico</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Americas</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">12/30/2009 04:34:02 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/6/2010 10:20:22 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>5.24</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4.24</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Mexico</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>United States</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Americas</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/4/2010 03:04:03 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/4/2010 05:42:30 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.11</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.11</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">United States</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>United States</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Americas</TD><TD class=xl66 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Closed</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/12/2010 02:20:26 PM</TD><TD class=xl67 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1/14/2010 12:41:12 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1.43</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1.43</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">United States</TD></TR></TBODY></TABLE>
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
You shouldn't need columns G or H or any filtering, try this version in F2 copied down

=IF(C2<>"Closed","N/A",NETWORKDAYS(D2,E2,INDEX(('[holiday calendar.xls]2010'!B$2:B$1000=A2)*('[holiday calendar.xls]2010'!C$2:C$1000),0))-1-MOD(D2,1)+MOD(E2,1))

extend the ranges beyond row 1000 if necessary but you can't specify the whole column (unless you're using Excel 2007)

holiday calendar workbook doesn't need to be open and holiday list can be sorted any way you want ('[holiday calendar.xls]2010'!C$2:C$1000 should contain only dates, no text)
 
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
623
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
P
  • Question Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
614
John_w
J
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,605
Messages
6,179,860
Members
452,948
Latest member
UsmanAli786

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