Count days except Sundays

C.S.

Board Regular
Joined
Nov 20, 2008
Messages
89
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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.

Board Regular
Joined
Nov 20, 2008
Messages
89
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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.....
 
Last edited:

Forum statistics

Threads
1,081,441
Messages
5,358,705
Members
400,508
Latest member
fish31

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top