Networkdays excluding zeros instead of weekends?

[tcannonk9]

Is there a way I can calculate the range of number of Days between nonzero columns? Ie..

column A is dates 1/1/2011 - 1/1/2012 Column B is a value either 0 or a whole number. Column B consists mostly of zeros. I would like a formula to calculate number of days between two dates in column A. For example if A1 =1/1/2011 and B1 =20 & the next whole number in column B is B10=1 I would like to calculate the number of days between A1 and A10. Is this possible? See example below.


date...... Runs.......Average Run Cycle

1/1/2012........1

1/2/2012........0

1/3/2012....... 0

1/4/2012....... 0

1/5/2012....... 0

1/6/2012....... 0

1/7/2012....... 0

1/8/2012....... 6

1/9/2012....... 8

1/10/2012...... 0

1/11/2012.......0

1/12/2012...... 0

1/17/2012...... 1

 

[dan_l]

so only do network days when column b does not equal zero?

 

[oldchippy]

Hi,


Try this in C2, copied down


=IF(B2=0,"",A2-$A$2)


If you want to include the first day then


=IF(B2=0,"",A2-$A$2+1)

 

[Faseeh]

Hi tcannonk9,


Welcome to the forums!!! If you are interested in entering two days and finding number of days (only zeros) then this might also work, Enter in F2 Start Date, In F3 End Date like this:

[pre]

Start Date	1/3/2012
End Date	1/10/2012
Days	        5[/pre]
....and in F4 enter this formula: =SUMPRODUCT((A2:A14>=F2)*(A2:A14<F3)*(B2:B14=0))

(Your data is located in A2:B14)


Hope this will help.


Regards,

Faseeh