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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]J - Start Date
[/TD]
[TD]K - End Date
[/TD]
[TD]L - Status
[/TD]
[TD]M - Bus Days
[/TD]
[/TR]
[TR]
[TD]10/26/13
[/TD]
[TD]12/3/13
[/TD]
[TD]Closed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/21/13
[/TD]
[TD]1/9/14
[/TD]
[TD]Closed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/27/14
[/TD]
[TD][/TD]
[TD]Open
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/21/14
[/TD]
[TD]3/27/14
[/TD]
[TD]Closed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/20/14
[/TD]
[TD][/TD]
[TD]Closed
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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