Dynamic Date Count

[Staggers72]

Hey I need to create a spreadsheet that will count days between start date and todays date with the following stipulations:

1.) Need to exclude sundays
2.) Need to exclude holidays

and the one that is giving me fits...
3.) exclude non workdays ..... these are days that we don't work because of weather and other reasons, like we don't want to work on a particular saturday but some saturdays we do....

Networkdays works well , because you simple add days to the 'holiday list' and they will not be counted. You can also correct the Networkdays counting of only weekdays by adding the number saturdays to the day count.....BUT not all saturdays are work days....hence my problem...

Below is my list of formulas

=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(D6&":"&C6))),{1},0)),1,0))
returns the number of days minus sunday

=NETWORKDAYS(D6,C6,'Days Worked Lists'!I5:I35)+J6
returns the number of days minus holidays and adds back saturdays

=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(D6&":"&C6))),{1,2,3,4,5,6},0)),1,0))
returns the number of saturdays in the date range....as you can see this is an array formula. This formula is represented by "J6" in above formula and I think is the problem, because when I add a Saturday to the Holiday/nonworkdays list, it is not recognized and therefore is counted.

=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(D6&":"&C6)))<>1))
another way of counting days minus sunday....easier to follow


This is what I think is happening....the formula represented by J6, which is an array, overides the holiday list of networkdays.....because what I was doing was adding in the saturdays that we dont work....

I need this spreedsheet to work for someone that can only enter date into excel....

I know I am rambling but I want to give everyone all the info, because I think this is something that others would really like to use. So in summary , I want a spread sheet that you enter a start date and holidays and non workdays and the end result is the number of days worked from a start date and todays date or end date.......the list of holidays and non work days needs to be added to on a regular basis. And the catch, some saturdays we work and some we don't.

Please help

 

[Ralajer]

Think I got it working.

Columns D,F,H are combined in J and checked against Column K the NETWORKDAYS.INTL Function which can specify the weekend but not available prior to Excel 2010.

D2 is just NETWORKDAYS function
F2 Counts the Saturdays over the range of dates. (credit to Chip Pearson ^[1]) To be added to NETWORKDAYS (D2)
H2 Counts the holidays that fall on Saturdays and are between the start and end date. This is to count the holidays that are exclude in the NETWORKDAYS function. To be subtracted from NETWORKDAYS(D2)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Start	End	Holdays	NETWORKDAY	+	Total Saturdays	-	Holidays & Saturday	=	Combined	NETWORKDAYS.INTL
2	7/7/2007	6/16/2011	Thu - 6/21/07	1018		206		3		1221	1221
3			Sun - 8/17/08
4			Sun - 8/17/08
5			Fri - 11/21/08
6			Thu - 1/8/09
7			Wed - 2/25/09
8			Tue - 4/14/09
9			Mon - 6/1/09
10			Sun - 7/19/09
11			Sat - 9/5/09
12			Fri - 10/23/09
13			Thu - 12/10/09
14			Wed - 1/27/10
15			Tue - 3/16/10
16			Mon - 5/3/10
17			Sun - 6/20/10
18			Sat - 8/7/10
19			Fri - 9/24/10
20			Sat - 9/25/10
...

Excel 2010

Cell Formulas
Range	Formula
B2	=TODAY()
D2	=NETWORKDAYS(A2,B2,C2:C20)
K2	=NETWORKDAYS.INTL(A2,B2,11,C2:C20)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

I also wrote a user defined function in VBA which reproduce the functionality of NETWORKDAYS.INTL (see this thread, if interested)

 

[barry houdini]

Here's a simpler formula for counting Sundays between C6 and D6

=INT((WEEKDAY(C6-1)+D6-C6)/7)

so to get a count of all days except Sundays you can use this

=D6-C6+1-INT((WEEKDAY(C6-1)+D6-C6)/7)

Then subtract all the non-Sunday holidays/non working days, i.e.

=D6-C6+1-INT((WEEKDAY(C6-1)+D6-C6)/7)-SUMPRODUCT((Y2:Z100>=C6)*(Y2:Z100<=D6)*(WEEKDAY(Y2:Z100)<>1))

assumes you have holidays in Y2:Y100 and other non-working days in Z2:Z100

 

[Staggers72]

the issue with both of these fixes is that they do not allow one to 'add' dates to the list, without having to know how to deal with an array.

Another way to look at is like this....say you are setting this up to start at the beginning of the year, you won't know which days during the year you will not work because of weather and or other issues. So their needs to be a list, that will have blank cells for someone else to enter in the non work days.

Each of these fixes, does not allow for input into the list....

So how do we do that....

 

[Staggers72]

Okay I figured it out using Barry's solution....thanks very much...now I have a spread sheet that any non work day (in the future) can be added to a defined list....AWESOME....