On Monday, February 9, 2015 at 11:54:07 AM UTC, Phil wrote:
I have a worksheet that contains two dates and open and closed, a target turnaround in days and am calculating days to target date and also instances that have missed target but are still open.

Example below (rows and columns included)

A B C D

1 TARGET
2 5
3 IN OUT DAYS TO TARGET OPEN FAILURES
4 28/01/2015 -3 1
5 28/01/2015 03/02/2015 #VALUE! #VALUE!
6 28/01/2015 03/02/2015 #VALUE! #VALUE!
7 30/01/2015 -1 1
8 02/02/2015 0 FALSE
9 03/02/2015 1 FALSE


Cells

A1 Target label

A2 Target in days (5)

Row 3 Labels

A4 booked in date

B4 booked out date

C4 Formula to calculate days to target, I am interested only in working
days (Mon to Fri) and removing Bank Holidays hence am referencing a range of Bank Holiday dates, B_H.
=IF(B4<"","CLOSED",(NETWORKDAYS(A4,TODAY(),B_H)-1)-$A$2)*-1

D4 Returns a 1 is something has missed target and is still open, so I can add these occurences up.
=IF(C4<0,IF(B4="","1",""))

I appreciate why the #VALUE! errors are there and they don't stop this working but are unslightly.
How do I get the formula to in column C to return CLOSED and in both columns supress the #VALUE! errors?

Any help is most welcome.

Thanks for reading.

Claus, your solution has worked perfectly.
Thanks for reading and replying so promptly.
Cheers,
Phil.