Networkdays and misleading results from blank column

R

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
 
R

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
 
T

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top