Count days except Sundays

[C.S.]

I'm building a tool to calculate the number of days after a given date. Once the date is entered, the list in the yellow shaded cells changes to the specified number of days from that date (see example below). I'm using in the shaded cells the following formula: =WORKDAY(AI4,10,AO4:AR13). AI4 being the given date (or current date), 10 is the number of days, and the range AO4:AR13 is the holidays to be excluded in count. I'm looking for a way to exclude all sundays from the count as well. Please help. Clint


<TABLE style="WIDTH: 193pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=258 border=0><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 548" width=15><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><TBODY><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 92pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" width=123 height=21>10 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 43pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=57>10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 11pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=15>is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 47pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow" width=63>10/21/2009</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>13 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">10/26/2009</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>20 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">11/4/2009</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>33 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">11/24/2009</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>35 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">11/27/2009</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>45 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">12/11/2009</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 92pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" width=123 height=21>50 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">12/18/2009</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>59 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">1/4/2010</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>60 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">1/5/2010</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>65 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">1/12/2010</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 92pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" width=123 height=21>89 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">2/17/2010</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>90 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">2/18/2010</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>100 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">3/4/2010</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" height=21>120 days from</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10/6/2009</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">is</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: yellow">4/1/2010</TD></TR></TBODY></TABLE>

 

[barry houdini]

WORKDAY excludes both Saturday and Sunday anyway so Sundays are already excluded, do you mean you want to include Saturday?

If so try like this where A1 is start date and B1 number of days to add and holidays is your range of holiday dates

=SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*2+COUNT(holidays))))>1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*2+COUNT(holidays))),holidays,0)),ROW(INDIRECT("1:"&B1*2+COUNT(holidays))))),B1)+A1

confirmed with CTRL+SHIFT+ENTER

 

[C.S.]

barry, exactly what i meant. count all days except sunday and specified range of holidays. I tried the formula you provided and get a #NUM! error message. I tried it by using the cells you used in the example (e.g. A1 for date, B1 for days, and I named my range "holidays". I really appreciate the help! And, I have a few other questions... What does an ampersand do when used in a function? And, what does it mean when you say "confirmed with CTRL+SHIFT+ENTER ? Thanks again for the help.

Clint

 

[barry houdini]

Hello Clint,

If you get #NUM! that means you probably haven't confirmed with CTRL+SHIFT+ENTER.

That key combination needs to be used with "array formulas", of which this is one. Put the formula in a cell,select that cell and press F2 key, then hold down CTRL and SHIFT keys and press ENTER. If done correctly then curly braces like { and } will appear around the formula in the formula bar....and you should get the correct result, if the result is just a number then format the cell in required date format.

Note that CTRL+SHIFT+ENTER needs to be re-applied if the formula is modified....

Ampersands in formulas are used to concatenate, so in this part

ROW(INDIRECT("1:"&B1*2+COUNT(holidays)))

if B1 is 10 and you have 8 holidays the B1*2+COUNT(holidays)=28 so you get

ROW(INDIRECT("1:"&28))

which becomes simply

ROW(INDIRECT("1:28"))

which generates an array of all integers from 1 to 28

This is added to A1 to generate an array of the next 28 dates. To simplify; the formula then evaluates each of these dates to see whether they are a) not Sundays and b) not holidays.....if both of those apply then the date is included in the final array, SMALL function then takes the B1th smallest, i.e. the 10th smallest in my example, thus giving you 10 days from the start date excluding Sundays and holidays as per the requirement.....