Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OR IF statement in EXCEL 2010 1

Status
Not open for further replies.

niteraven

Technical User
Oct 26, 2006
92
US
Hello

I am trying to do the following in excel:
1. test 2 cells for null value (should contain date)
2. After testing both cells for null use networkdays to figure the amount of dates between

Here is the statements i have tried to no avail:

=IF(ISBLANK(G857:H857), 0, NETWORKDAYS(G857,H857))
That tests both fields like an and statement both fields have to be null in order to return 0
so i tried:
=OR((IF(ISBLANK(G859), 0,NETWORKDAYS(G859,H859))) , (IF(ISBLANK(h859), 0 , NETWORKDAYS(G859,H859))))
Does not work shows as text not a formula

ANy help is appreciated!

THanks
Raven
 
Right-Click on the cell, go to 'Format Cells...' - what do you see?
You should have it as 'General' in order for your formula to work, not as Text.

Have fun.

---- Andy
 
Ok it is now formatted as general

But now it is returning True instead of days between two cells (should be how many days between start and end date)

ANy ideas?
 
I have just tried the statement this way it errors with too many arguements:

=IF(ISBLANK(OR(H4),(G4)), "NO ESTIMATE", NETWORKDAYS(G4,H4)))

I would think there is an easy way to do this but i have googled and searched thru this forum. No dice.

Thanks
Raven
 

How about...

Code:
=IF(OR(G857="",H857=""),0,NETWORKDAYS(G857,H857))

Randy
 

Another possibility:

Code:
=IF(OR(ISBLANK(G857),ISBLANK(H857)),0,NETWORKDAYS(G857,H857))

Randy
 
[tt]
=IF(OR(ISBLANK(H4),ISBLANK((G4)), "NO ESTIMATE", NETWORKDAYS(G4,H4)))
[/tt]
the OR() function must contain expressions that return TRUE or FALSE.

Hance for TWO expressions...
[tt]
OR(FALSE,FALSE) retuns FALSE
OR(TRUE,FALSE) retuns TRUE
OR(FALSE,TRUE) retuns TRUE
OR(TRUE,TRUE) retuns TRUE
[/tt]
The OR needs EVERY argument FALSE to erturn FALSE. Any other combination returns TRUE.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Therefore, you need to use the AND command, not the OR to get the NETWORKDAYS command to work.
Code:
=IF(AND(ISBLANK(G4),ISBLANK(H4)), NETWORKDAYS(G4,H4), "NO ESTIMATE")
AND(FALSE,FALSE) retuns FALSE - "NO ESTIMATE"
AND(TRUE,FALSE) retuns FALSE - "NO ESTIMATE"
AND(FALSE,TRUE) retuns FALSE - "NO ESTIMATE"
AND(TRUE,TRUE) retuns TRUE - NETWORKDAYS(G4,H4)

 
Thanks guys........

Zelgar:This worked. Thanks. I guess i could not see the forest for the trees.

Raven
 

zelgar,

The code you posted says...
If G4 is blank AND
If H4 is blank THEN
perform the NETWORKDAYS function - which will return 0.

The code I posted says...
If G857 is blank OR
If H857 is blank THEN
do NOT perform the NETWORKDAYS function.

Both cells need to have a value to work as the OP wants.






Randy
 
I realized that my results were backwards after I posted it, but it looks like the OP was able to work around it so I didn't post a correction.
 
The isnumber(G4) function might be better if there is a risk of blank cells not being properly blank or not having proper dates in.

Gavin
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top