Networkdays and misleading results from blank column
 

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

Networkdays and misleading results from blank column
 

On Mon, 29 Mar 2010 11:23:01 -0700, Rosemarie
wrote:

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

Networkdays and misleading results from blank column
 

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


Gord Dibben MS Excel MVP

On Mon, 29 Mar 2010 11:23:01 -0700, Rosemarie
wrote:

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

Networkdays and misleading results from blank column
 

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))

--
Biff
Microsoft Excel MVP


"Rosemarie" wrote in message
...
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