OR IF statement in EXCEL 2010 1

[niteraven]

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

 

[Andrzejek]

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

 

[niteraven]

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?

 

[niteraven]

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

 

[randy700]

How about...

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

Randy

 

[randy700]

Another possibility:

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

Randy

 

[SkipVought]

[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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

Therefore, you need to use the AND command, not the OR to get the NETWORKDAYS command to work.

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

 

[niteraven]

Thanks guys........

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

Raven

 

[randy700]

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

 

[zelgar]

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.

 

[Gavona]

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