Pending, In Process or Completed based on condition

[Vishaal]

Hi we have the following sheet where we want to track work is pending or not when date is over

Condition is
1. IF(A+7=TODAY DATE)
2. IF(A+7<TODAY DATE)
3. IF(F=BLANK)
4. If there is any Sunday or Holidays then it will not count that day (I think we can use networkdays or any other)

If all above condition match then in column h, Pending
and if we have received the order then completed

and if A:G have data but order not received and A+7>today() then “In Process”.

Previous Post Here : True or False if condition match
Some questions change so creating new thread.

 

[Vishaal]

sheet are here

New Microsoft Excel Worksheet - Copy (version 1).xlsb
	A	B	C	D	E	F	G
1	Date	Order	P Name	Qty		Order Received on	Recd. Qty
2	27/4/2023	Given	TEST 1	100,250		29/4/2023	100,250
3	29/4/2023	Given	TEST 2	250,325		4/5/2023	250,300
4	4/7/2023	Given	TEST 3	650214
5	5/7/2023	Given	TEST 4	956321		10/7/2023	956320
6	5/7/2023	Given	TEST 5	658,745
7	6/7/2023	Given	TEST 6	500
8	11/7/2023	Given	TEST 7	54800
9	12/7/2023	Given	TEST 8	65,914
Sheet4

 

[awoohaw]

Try this.
Please note that i think you "pending criteria" should be looking for non bank cells in A:D, not A:G.
Please note that this requires the CNTL-SHFT-ENTR keystroke to enter the formula correctly.

Book2
	A	B	C	D	E	F	G	H	I
1	Date	Order	P Name	Qty		Order Received on	Recd. Qty		Condition is
2	Thu 2023-04-27	Given	TEST 1	100250		Sat 2023-04-29	100250	Completed	1. IF(A+7=TODAY DATE)
3	Sat 2023-04-29	Given	TEST 2	250325		Thu 2023-05-04	250300	Completed	2. IF(A+7<TODAY DATE)
4	Tue 2023-07-04	Given	TEST 3	650214				PENDING	3. IF(F=BLANK)
5	Wed 2023-07-05	Given	TEST 4	956321		Mon 2023-07-10	956320	Completed	4. If there is any Sunday or Holidays then it will not count that day (I think we can use networkdays or any other)
6	Wed 2023-07-05	Given	TEST 5	658745				In Process
7	Thu 2023-07-06	Given	TEST 6	500				In Process	If all above condition match then in column h, Pending
8	Tue 2023-07-11	Given	TEST 7	54800				In Process	and if we have received the order then completed
9	Wed 2023-07-12	Given	TEST 8	65914				In Process
10									and if A:G have data but order not received and A+7>today() then “In Process”. I think this should be A:D, I use A:E but count only 4 non blank cells.
Sheet2

Cell Formulas
Range	Formula
H2:H9	H2	=IF(AND(WORKDAY.INTL(A2,6,"0000011")<TODAY(),F2=""),"PENDING", IF(F2<>"","Completed", IF(SUM(--(A2:E2<>""))=4,"In Process","")))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Vishaal]

Thanks awoohaw

It's working

 

[awoohaw]

Thanks awoohaw

It's working

I'm pleased you found a solution here.
Best Wishes!