Networkdays Formula IF/OR Cell has Open or Closed

[EvanDef]

Hello Excel Gurus!

I am befuddled over an IF/OR function in Column M (M2) of Networkdays where if Column L (L2) has "Closed" as a status Networkdays calculates the difference between J2(start date),K2(end date),HOLIDAYS in M2. But if Column L (L2) has "Open" as a status Networkdays calculates the difference between J2(start date),TODAY(end date),HOLIDAYS. Also, if Column L is marked as "Closed" but there is no date populated in the corresponding end date cell (K), ERROR should be returned to prompt data entry corrections.

J - Start Date	K - End Date	L - Status	M - Bus Days
10/26/13	12/3/13	Closed
11/21/13	1/9/14	Closed
1/27/14		Open
2/21/14	3/27/14	Closed
3/20/14		Closed

<tbody>
</tbody>

I appreciate your time, Thanks!

 

[Andrew Fergus]

Hi

How about somerthing like this:
=IF(L2="Open",NETWORKDAYS(J2,NOW()),IF(LEN(K2)=0,"ERROR",NETWORKDAYS(J2,K2)))

Amend the formula to suit your own holidays etc.

I trust this helps
Andrew

 

[EvanDef]

Andrew,

That worked like a charm! I tried to nest my IF statements but I kept getting a negative value. I completely overlooked the character length (LEN) value and NOW function as options. Thanks for your direction.

Regards, Evan