Days Delay excel formula as per given data

[Vishaal]

HI, we have the following data where we count the delay but don't want to count the Sunday

Date	Order	P Name	TG	Qty		Recd. Date	Recd. Qty		Delay
27/4/2010	Given	TEST 1	4/29/2010	500		4/27/2010	500
9/5/2010	Given	TEST 2	5/16/2010	200		5/20/2010	150
9/5/2010	Given	TEST 3	5/16/2010	150		5/17/2010	150
10/5/2010	Given	TEST 4	5/17/2010	600				Not recd. till now and date is over
10/5/2010	Given	TEST 5	5/17/2010	254		5/16/2010	254
12/5/2010	Given	TEST 6	5/19/2010	654		5/18/2010	654
12/5/2010	Given	TEST 7	5/19/2010	951		6/10/2010	950
12/5/2010	Given	TEST 8	5/19/2010	757		6/15/2010	757

Delay will be count if column g will be bigger then column D
and also in Column G5, its blank but date is over so want to count delay in this cell also

 

[awoohaw]

thanks for using the xl2bb add in, but you did not select show mini sheet (to show column letters and row numbers) so we have to guess what is column G and what is column D. I will attempt an answer.

try this:
I am not sure if networkdays.intl is in 2010 or 2007. The help documentation says it is available in 2010.

Book1
	B	C	D	E	F	G	H	I	J
1	Order	P Name	TG	Qty		Recd. Date	Recd. Qty		Delay
2	Given	TEST 1	2010-04-29	500		2010-04-27	500
3	Given	TEST 2	2010-05-16	200		2010-05-20	150		4
4	Given	TEST 3	2010-05-16	150		2010-05-17	150		1
5	Given	TEST 4	2010-05-17	600				Not recd. till now and date is over
6	Given	TEST 5	2010-05-17	254		2010-05-16	254
7	Given	TEST 6	2010-05-19	654		2010-05-18	654
8	Given	TEST 7	2010-05-19	951		2010-06-10	950		20
9	Given	TEST 8	2010-05-19	757		2010-06-15	757		24
Sheet4

Cell Formulas
Range	Formula
J2:J9	J2	=IF(G2>D2,NETWORKDAYS.INTL(D2,G2,11),"")

 

[Vishaal]

Thanks, we will copy mini sheet from next time. Excel version is 2007

But in row 4 date is over so we need delay there also

Help please

 

[awoohaw]

But in row 4 date is over so we need delay there also

I'm not sure what you mean. I show as being 1 day over already. Please explain.

 

[Vishaal]

Oh sorry… my mistake

I mean in row 5 where we have not received entry but date is over

Because target date is 2010-05-17 and we have received the product till 2010-05-20,

Suppose today is 2010-05-20 and we have not received the order till now so we want this delay also

 

[awoohaw]

Okay. So if the received date is blank you want to use the current system date?

try this:

Mr excel questions 41.xlsm
	B	C	D	E	F	G	H	I	J
1	Order	P Name	TG	Qty		Recd. Date	Recd. Qty		Delay
2	Given	TEST 1	2010-04-29	500		2010-04-27	500
3	Given	TEST 2	2010-05-16	200		2010-05-20	150		4
4	Given	TEST 3	2010-05-16	150		2010-05-17	150		1
5	Given	TEST 4	2010-05-17	600				Not recd. till now and date is over	4092
6	Given	TEST 5	2010-05-17	254		2010-05-16	254
7	Given	TEST 6	2010-05-19	654		2010-05-18	654
8	Given	TEST 7	2010-05-19	951		2010-06-10	950		20
9	Given	TEST 8	2010-05-19	757		2010-06-15	757		24
Vishaal

Cell Formulas
Range	Formula
J2:J9	J2	=IF(OR(G2="",G2>D2),NETWORKDAYS.INTL(D2,IF(G2="",TODAY(),G2),11),"")

 

[Vishaal]

Thanks awoohaw, its work for me​

 

[awoohaw]

I happy you found an answer! Thanks for the feedback.