Excel Formula

[Guest]

I need help creating a formula. the end result is to have the # of
business days between two dates. However if 1 column is not filled in yet
(because the day has not come up yet) the return is a -27969.

Ex: Col. A = 15-Mar-07 and Col. B is blank (will be filled in later) Col. C
would like to return a true value or "ok". Current formula in column C is
=NETWORKDAYS(A1,B1) and when column B is filled in with lets say with
20-Mar-07 col C will be 4 days.

how do I create a formula where it does not return a -xxxxx and replace it
with "ok" or "0"?
Thank you ~

 

[Guest]

=if(or(isblank(A1),isblank(B1)),"",networkdays(A1,B1))

Dave

 

[Pete_UK]

Try something like this:

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

You might like to test for A1 being blank also, like this:

=IF(OR(A1="",B1=""),"ok",NETWORKDAYS(A1,B1)

Hope this helps.

Pete

 

[Pete_UK]

Sorry, missed the closing bracket off the end of both formulae.

Pete

 

[Guest]

Pete ~

I am now getting a #Value error in some of my cells. Is this something you
can help me figure out?

A7 is populated, G7 is not H7 is where I want the value to be "ok" OR
provide the #of business days between the two cell dates.

Your assistance is appreciated. Thank you for your time!

 

[Pete_UK]

The #VALUE error implies that the data in A or B is not as expected,
i.e. you might have something that looks like a date but is really a
date in text format - you can't do any arithmetic on text.

Alternatively, you may have a space in a cell which makes it look
empty but it isn't - this won't return "ok" as expected because the
cell is not a true blank, so the second half of the formula will try
to evaluate and return the error.

I suggest you look closely at the data in A or B on the rows where you
get the error.

Hope this helps.

Pete

 

[Guest]

THANK YOU!!

I think I finally got it! I really appreciate your time!

~ Cori

 

[Pete_UK]

Glad to be of help - thanks for feeding back.

Pete