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.

Hi Phil,

Am Mon, 9 Feb 2015 03:53:59 -0800 (PST) schrieb Phil:

=IF(B4<"","CLOSED",(NETWORKDAYS(A4,TODAY(),B_H)-1)-$A$2)*-1

try:
=IF(ISNUMBER(B4),"CLOSED",-(NETWORKDAYS(A4,TODAY(),B_H)-$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",""))

try:
=IF(AND(C4<0,LEN(B4)=0),1,"")

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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.

Hi Phil,

Am Mon, 9 Feb 2015 08:21:35 -0800 (PST) schrieb Phil:

Thanks for reading and replying so promptly.

you are welcome. I am always glad to help.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional