Networkdays and misleading results from blank column

[Rosemarie]

Hello, I am working with two columns of dates and need to work out the number
of working days between two dates, column A and column B with result in
Column C. However in some instances column B is blank.
Example: Column A = 14/01/2010 Column B = 23/01/2010 Result = 7 & thats
ok. Problem is when column B is blank because the result would then come
back as -28708.

I am using the following formula: =NETWORKDAYS(A1,B1,Holidays). How could
I add a condition to this formula that would translate 'if A1 and B1 are not
blank then calculate number of days, if B1 is blank return a 0 (zero) result'

Reason I need a zero or blank return is because afterward I'm working out
the average number of days.
Many thanks

 

[Ron Rosenfeld]

Hello, I am working with two columns of dates and need to work out the number
of working days between two dates, column A and column B with result in
Column C. However in some instances column B is blank.
Example: Column A = 14/01/2010 Column B = 23/01/2010 Result = 7 & thats
ok. Problem is when column B is blank because the result would then come
back as -28708.

I am using the following formula: =NETWORKDAYS(A1,B1,Holidays). How could
I add a condition to this formula that would translate 'if A1 and B1 are not
blank then calculate number of days, if B1 is blank return a 0 (zero) result'

Reason I need a zero or blank return is because afterward I'm working out
the average number of days.
Many thanks

Something like (not tested):

=if(count(a1:b1)=2, NETWORKDAYS(A1,B1,Holidays),0))

or maybe

=max(0,NETWORKDAYS(A1,B1,Holidays)))
--ron

 

[Gord Dibben]

=IF(B1="","",NETWORKDAYS(A1,B1,Holidays)


Gord Dibben MS Excel MVP

 

[T. Valko]

if B1 is blank return a 0 (zero) result'

Reason I need a zero or blank return is because
afterward I'm working out the average number of days.

If you return 0 when there aren't 2 dates in the cells then that might skew
your average.

How about returning a blank?

=IF(COUNT(A1,B1)<2,"",NETWORKDAYS(A1,B1,Holidays))