Excel Formula
 

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 ~

Excel Formula
 

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Oski" wrote:

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 ~

Excel Formula
 

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

On Mar 16, 4:32 pm, Oski wrote:
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 ~

Excel Formula
 

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

Pete

On Mar 16, 4:45 pm, "Pete_UK" wrote:
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

On Mar 16, 4:32 pm, Oski wrote:



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 ~- Hide quoted text -

- Show quoted text -

Excel Formula - Revisted
 

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" wrote:

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

Pete

On Mar 16, 4:45 pm, "Pete_UK" wrote:
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

On Mar 16, 4:32 pm, Oski wrote:



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 ~- Hide quoted text -

- Show quoted text -

Excel Formula - Revisted
 

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

On Mar 16, 8:55 pm, Oski wrote:
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" wrote:
Sorry, missed the closing bracket off the end of both formulae.

Pete

On Mar 16, 4:45 pm, "Pete_UK" wrote:
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

On Mar 16, 4:32 pm, Oski wrote:

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 ~- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Excel Formula - Revisted
 

THANK YOU!!

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

~ Cori

"Pete_UK" wrote:

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

On Mar 16, 8:55 pm, Oski wrote:
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" wrote:
Sorry, missed the closing bracket off the end of both formulae.

Pete

On Mar 16, 4:45 pm, "Pete_UK" wrote:
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

On Mar 16, 4:32 pm, Oski wrote:

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 ~- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Excel Formula - Revisted
 

Glad to be of help - thanks for feeding back.

Pete

On Mar 17, 2:07 am, Oski wrote:
THANK YOU!!

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

~ Cori



"Pete_UK" wrote:
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

On Mar 16, 8:55 pm, Oski wrote:
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" wrote:
Sorry, missed the closing bracket off the end of both formulae.

Pete

On Mar 16, 4:45 pm, "Pete_UK" wrote:
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

On Mar 16, 4:32 pm, Oski wrote:

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 ~- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -